I have a MySQL table with the following definition: mysql> desc person; +——–+———+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——–+———+-…
Select two columns group by (city,sku) , but just show the top 5 registers by (city)
I need to do a query to show the top seller products group by city. The “trap” is, I just can show the top 5 by city: I start using this query: select ORD.shipping_address_city city, ORDL.sku,…
Number based on condition
I’m trying to generate a number based on a condition. When there is yes in column ‘Stop’ in the partition of a Client ordered by Start_Date, the Dense Rank has to start over. So I tried several things but it’s stil not what I want. My table with current number and expected number The q…
Trying to Update Case When Then, based on matching IDs in two tables
I am trying to updated IDs in a table named Price, based on matches of IDs in a table named CW. Here is the SQL that I’m testing. UPDATE Price SET ISN = (case when CW.id_cd = ‘ISN’ THEN CW….
Language issues when running SQL statements from installshield
I have an installshield installation that connects to a SQL Server instance and inserts words from different languages. After the import runs When I query the data from the database characters from …
Not return row from table when use the case when condition
I have a basic question. I want to return data from my table when the condition is true but when it is not I got the null row, but I don’t want that. This is my example: select case when coalesce(…
SQL (Persons share the same birthday but they are exactly 100 years older or younger)
i have this table: How do I fetch a list of Persons who have the same dob and exactly 100 years older or younger? Expected rows: The difference between dob should be 100 years Answer If the DOB is a date data type, would suggest using TRUNC and ADD_MONTHS.
Custom SQL for quarter count starting from previous month
I need to create a custom quarter calculator to start always from previous month no matter month, year we are at and count back to get quarter. Previous year wuarters are to be numbered 5, 6 etc So the goal is to move quarter grouping one month back. Assume we run query on December 11th, result should be: YEA…
NZ function in T-SQL where ValueIfNull is NOT specified
I am working on an MS Access to SQL Server Migration project and am currently in the process of converting a complex query into T-SQL from MS Access. Now I’m quite familiar with how Nz works in Access …
TRIGGER that checks if that delete removes all the table values
Basicly I just don’t wanna allow that DELETE FROM X delete ALL rows. For example: DELETE FROM X WHERE ID = 3 OR ID = 4; –> Allowed because don’t try to remove all table. DELETE FROM X; –> Not …