Complicated query issue: need to specify strict return
-
I have a table i am querying, and a prety odd set i need to return. the columns i am interested in are the following types: int, nvarchar, datetime,datetime. the int has a pretty normal progression, 1,2,3,4,5. the nvarchar contains only 2 possible values, we'll say red and blue. now, the data lines up something like this: 1 blue 2 blue 1 red 3 blue 2 red 3 red ie, no particular order, but every number listed in the int coulmn has a red and a blue row, so a normal query would return 1 blue 2 blue 3 blue 1 red 2 red 3 red if ordering by the colors now, the two date times are a strat date and end date, and they line up weird. the blue and red of each int do not have the same range, but are considered related. however, all of the dates for a color tie together, so if the enddate for 1 blue is 4/25/2008, then the startdate for 2 blue is 4/26/2008. same goes for red, except what i end up with is something like this 1 blue 2/23/2008 3/12/2008 1 red 2/25/2008 3/20/2008 what i need to do is write a query that returns only 2 values, where they are both less than todays date, one is blue and one is red, and they both have the same int value. now, the query i am using looks like this:
select top 2 * from [table] where enddate < cast('4/15/2008 12:59:59.999' as datetime) order by enddate descthis particular one is right on the cusp of one of the transition dates, and the result i am getting is: 1 red 2 blue when i need 1 red 1 blue or 2 red 2 blue. the int must always be the same and the nvarchar must always be different. is there a way to force this in a query? what i end up needing is a query that returns______________________ Mr Griffin, eleventy billion is not a number...:wtf:
-
I have a table i am querying, and a prety odd set i need to return. the columns i am interested in are the following types: int, nvarchar, datetime,datetime. the int has a pretty normal progression, 1,2,3,4,5. the nvarchar contains only 2 possible values, we'll say red and blue. now, the data lines up something like this: 1 blue 2 blue 1 red 3 blue 2 red 3 red ie, no particular order, but every number listed in the int coulmn has a red and a blue row, so a normal query would return 1 blue 2 blue 3 blue 1 red 2 red 3 red if ordering by the colors now, the two date times are a strat date and end date, and they line up weird. the blue and red of each int do not have the same range, but are considered related. however, all of the dates for a color tie together, so if the enddate for 1 blue is 4/25/2008, then the startdate for 2 blue is 4/26/2008. same goes for red, except what i end up with is something like this 1 blue 2/23/2008 3/12/2008 1 red 2/25/2008 3/20/2008 what i need to do is write a query that returns only 2 values, where they are both less than todays date, one is blue and one is red, and they both have the same int value. now, the query i am using looks like this:
select top 2 * from [table] where enddate < cast('4/15/2008 12:59:59.999' as datetime) order by enddate descthis particular one is right on the cusp of one of the transition dates, and the result i am getting is: 1 red 2 blue when i need 1 red 1 blue or 2 red 2 blue. the int must always be the same and the nvarchar must always be different. is there a way to force this in a query? what i end up needing is a query that returns______________________ Mr Griffin, eleventy billion is not a number...:wtf:
Change your order by to include the number field as the first portion before the enddate, e.g.
order by id, enddate.Deja View - the feeling that you've seen this post before.
-
I have a table i am querying, and a prety odd set i need to return. the columns i am interested in are the following types: int, nvarchar, datetime,datetime. the int has a pretty normal progression, 1,2,3,4,5. the nvarchar contains only 2 possible values, we'll say red and blue. now, the data lines up something like this: 1 blue 2 blue 1 red 3 blue 2 red 3 red ie, no particular order, but every number listed in the int coulmn has a red and a blue row, so a normal query would return 1 blue 2 blue 3 blue 1 red 2 red 3 red if ordering by the colors now, the two date times are a strat date and end date, and they line up weird. the blue and red of each int do not have the same range, but are considered related. however, all of the dates for a color tie together, so if the enddate for 1 blue is 4/25/2008, then the startdate for 2 blue is 4/26/2008. same goes for red, except what i end up with is something like this 1 blue 2/23/2008 3/12/2008 1 red 2/25/2008 3/20/2008 what i need to do is write a query that returns only 2 values, where they are both less than todays date, one is blue and one is red, and they both have the same int value. now, the query i am using looks like this:
select top 2 * from [table] where enddate < cast('4/15/2008 12:59:59.999' as datetime) order by enddate descthis particular one is right on the cusp of one of the transition dates, and the result i am getting is: 1 red 2 blue when i need 1 red 1 blue or 2 red 2 blue. the int must always be the same and the nvarchar must always be different. is there a way to force this in a query? what i end up needing is a query that returns______________________ Mr Griffin, eleventy billion is not a number...:wtf:
Didn't actually try running this, but you can give it a whirl: select top 2 a.* from [table] a INNER JOIN [table] b ON a.int = b.int AND a.nvarchar <> b.nvarchar AND b.enddate < cast('4/15/2008 12:59:59.999' as datetime) where a.enddate < cast('4/15/2008 12:59:59.999' as datetime) order by enddate desc
-
Didn't actually try running this, but you can give it a whirl: select top 2 a.* from [table] a INNER JOIN [table] b ON a.int = b.int AND a.nvarchar <> b.nvarchar AND b.enddate < cast('4/15/2008 12:59:59.999' as datetime) where a.enddate < cast('4/15/2008 12:59:59.999' as datetime) order by enddate desc
your code is a thing of beauty :) i modified it to run against the table i was using and was getting the same results i was getting before, whihc confused me because looking at it, it definatley said exaclty what i needed. so i opened a new view and plugged it in and started playing with it. the first thing i did was change it to show the results from a and b, and bingo, there it was. with this, i dont need the top 2 rows, just the top 1 with the nd date set to order by desc, and tada, i had the exact results i was looking for. so here is what i used:
SELECT TOP (1) a.nvarchar, b.nvarchar AS nvarchar2, b.int, a.int AS int2, a.StartDate, b.StartDate AS StartDate2, a.EndDate, b.EndDate AS EndDate2 FROM [table] AS a INNER JOIN [table] AS b ON a.int = b.int AND a.nvarchar <> b.nvarchar AND b.EndDate < getdate() AND a.EndDate < GETDATE() ORDER BY int2 DESCThank you very much for this, you saved me a massive headache. And thank you to everyone that replied, i appreciate any input :D______________________ Mr Griffin, eleventy billion is not a number...:wtf: