How to get a multi colum select with one colum SELECT DISTINCT with only one command ?
-
Hi all, I want to get a multicolumn selection but with the behavior of the DISTINCT cmd only on the first column, something like that
SELECT DISTINCT order_id, date FROM Orders WHERE .....but the distinction should only refer on the "order_id" and not even on the "date" . How does the sql-cmd looks like ? thanks in advance Frank -
Hi all, I want to get a multicolumn selection but with the behavior of the DISTINCT cmd only on the first column, something like that
SELECT DISTINCT order_id, date FROM Orders WHERE .....but the distinction should only refer on the "order_id" and not even on the "date" . How does the sql-cmd looks like ? thanks in advance FrankCan you give us a sample of what data exists in the table, and then what data you want after the query has been run? Because what you have doesn't seem to make sense...if your data looks like this: order_id - date 1 - 1/1/2008 2 - 1/1/2008 2 - 1/2/2008 then which date are you going to want to display? If you distinct on only the order_id (which isn't really possible using a distinct, you would have to use a group by) then the SQL doesn't know which date you want...(which is why it's not possible using Distinct)....so please supply us some more info so we can help.
-
Hi all, I want to get a multicolumn selection but with the behavior of the DISTINCT cmd only on the first column, something like that
SELECT DISTINCT order_id, date FROM Orders WHERE .....but the distinction should only refer on the "order_id" and not even on the "date" . How does the sql-cmd looks like ? thanks in advance FrankThis doesn't make sense. Either all of the data is distinct or none of it is. If you want a single order_id, then you can only retrieve one date otherwise this order will span multiple lines.
Deja View - the feeling that you've seen this post before.
-
Can you give us a sample of what data exists in the table, and then what data you want after the query has been run? Because what you have doesn't seem to make sense...if your data looks like this: order_id - date 1 - 1/1/2008 2 - 1/1/2008 2 - 1/2/2008 then which date are you going to want to display? If you distinct on only the order_id (which isn't really possible using a distinct, you would have to use a group by) then the SQL doesn't know which date you want...(which is why it's not possible using Distinct)....so please supply us some more info so we can help.
Hi Kschuler, my problem is that I have to show the date and I have to show the order_id but only once. I know, in most cases it doesn't makes sense. But for my application it doesn't matters which date has to be displayed when double entries exists. The central point is that all entries have to show a date multiple entries or not. This is the crux. Now I think there is no SQL solution for that problem. Now I filter the recordset. Thank you for your help
-
Hi Kschuler, my problem is that I have to show the date and I have to show the order_id but only once. I know, in most cases it doesn't makes sense. But for my application it doesn't matters which date has to be displayed when double entries exists. The central point is that all entries have to show a date multiple entries or not. This is the crux. Now I think there is no SQL solution for that problem. Now I filter the recordset. Thank you for your help
You can accomplish it with something like this:
SELECT order_id, MAX(date) FROM tablename GROUP BY order_idThe idea is that you will return one order_id and the largest date with that order_id. I know it would work for a number, I'm just not sure how a date reacts to the MAX() function...you could also use MIN if you want the smallest date...and if you did thisSELECT order_id, MAX(date), COUNT(*) FROM tablename GROUP BY order_idIt would also display how many dates exist for that order id. So maybe you need to do some research on GROUP BY and how it affects date datatypes. Hope this helps. -
You can accomplish it with something like this:
SELECT order_id, MAX(date) FROM tablename GROUP BY order_idThe idea is that you will return one order_id and the largest date with that order_id. I know it would work for a number, I'm just not sure how a date reacts to the MAX() function...you could also use MIN if you want the smallest date...and if you did thisSELECT order_id, MAX(date), COUNT(*) FROM tablename GROUP BY order_idIt would also display how many dates exist for that order id. So maybe you need to do some research on GROUP BY and how it affects date datatypes. Hope this helps.