December 11, 2012 at 9:55 am
Hi All,
I am trying to write a query to pull out the next order but it isn't working if the next order is equal to the order that I am omitting. It works when the next order is different but when pulling the next order that is equal to 'Glucose' with a entered_date of ''2012-12-11 06:33:27.108', it is not displaying.
Thanks for any help!
SELECT
t.name, MAX(date)
FROM
(SELECT TOP 1
o.name, MAX(o.entered_date) as date
FROM
dbo.orders o
WHERE
o.clientid= 296400200
AND o.name <> 'GLUCOSE'
AND o.entered_date <> '2012-12-11 06:35:55.120'
GROUP BY o.name
ORDER BY date desc) t
GROUP BY t.name
December 11, 2012 at 10:04 am
If I understand your logic correctly, all orders with 'GLUCOSE' field value would be omitted because of the nature of your AND filters.
December 11, 2012 at 10:14 am
Andre...thank you for replying!
I have a few orders in the table that are sorted by Date desc.
In the table the first two orders in the table are:
1. Glucose - 2012-12-11 06:35:55.120
2. Glucose - 2012-12-11 06:33:27.108
3. Sodium - 2012-12-11 06:20:18.124
4. Creatinine - 2012-12-11 06:14:18.101
Since the Glucose are number 1 and 2 in the table, I want to pull out
the second Glucose level. Right now the query omits the second Glucose level
and pulls the Sodum level next.
Thanks,
Tony
December 11, 2012 at 10:36 am
eusanpe1 (12/11/2012)
Hi All,I am trying to write a query to pull out the next order but it isn't working if the next order is equal to the order that I am omitting. It works when the next order is different but when pulling the next order that is equal to 'Glucose' with a entered_date of ''2012-12-11 06:33:27.108', it is not displaying.
Thanks for any help!
SELECT TOP 1
o.name, MAX(o.entered_date) as date
FROM
dbo.orders o
WHERE
o.clientid= 296400200
AND o.name <> 'GLUCOSE'
AND o.entered_date <> '2012-12-11 06:35:55.120'
GROUP BY o.name
ORDER BY date desc)
I'm not sure I understand this very well so let's just break down your query a little:
So your derived table is asking SQL Server to bring back the latest ordered product that's isn't called GLUCOSE.
I don't see the purpose of the outer query.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 11, 2012 at 10:57 am
Hi...
I am looking to pull the one order with the next maximum date.
I am running a separate program that has this sql code embedded.
It passes variables to the code. I have variables defined in my other
program as 'OrderName' and 'OrderDate'. My program runs in a loop
so in the example displayed:
If the OrderName = 'Glucose' and the OrderDate = '2012-12-11 06:35:55.120' then
I want to return Glucose and 2012-12-11 06:33:27.108
If the Order Name = 'Glucose' and the OrderDate = '2012-12-11 06:33:27.108' then
I want to return Sodium and 2012-12-11 06:20:18.124
and so forth.
I hope this makes sense.
Tony
December 11, 2012 at 11:05 am
I believe the following will do what you need...
SELECT TOP 1
o.name, MAX(o.entered_date) as date
FROM
dbo.orders o
WHERE
o.clientid= 296400200
AND o.entered_date <> '2012-12-11 06:35:55.120'
GROUP BY o.name
ORDER BY date desc)
If you only care about order date there is no reason to filter by the item ordered.
December 11, 2012 at 11:06 am
Get rid of the "o.name <> 'GLUCOSE'" part. It's the primary problem here.
Change the "o.entered_date <> '2012-12-11 06:35:55.120'" to a simple less-than. Should be "o.entered_date < '2012-12-11 06:35:55.120'". "<" not "<>". You want the next one reverse date sequence.
Simplify the query. The outer query isn't doing anything useful.
Final result:
SELECT TOP 1
o.name, o.entered_date as date
FROM
dbo.orders o
WHERE
o.clientid= 296400200
AND o.entered_date < '2012-12-11 06:35:55.120'
ORDER BY date desc;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 11, 2012 at 11:10 am
GSquared (12/11/2012)
Change the "o.entered_date <> '2012-12-11 06:35:55.120'" to a simple less-than. Should be "o.entered_date < '2012-12-11 06:35:55.120'". "<" not "<>". You want the next one reverse date sequence.Simplify the query. The outer query isn't doing anything useful.
Final result:
SELECT TOP 1o.name, o.entered_date as date
FROM
dbo.orders o
WHERE
o.clientid= 296400200
AND o.entered_date < '2012-12-11 06:35:55.120'
ORDER BY date desc;
Came to this same conclusion after looking at it further.
December 11, 2012 at 11:11 am
Thank you both.
I will try your suggestions and let you know.
Tony
December 11, 2012 at 11:35 am
Worked beautifully. I like simplicity.
Thank you both very much.
Tony
December 11, 2012 at 11:38 am
I concur with the other solution. Including the AND clause "AND o.name <> 'GLUCOSE'" would omit all possibility of results containing the name field value "GLUCOSE"
Cheers,
Andre Ranieri
December 11, 2012 at 11:42 am
You're welcome. Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply