Answered by:
Question about designign a SQL query

Question
-
Hello,
I have a table like this
column1 column2
a 2010-04-15
a 2010-04-17
b 2010-04-17
b 2010-04-16
b 2010-04-20
c 2010-04-19
I want to display the different columns with a data the most near the current date ( 2010-04-15), like this:
a 2010-04-15
b 2010-04-16
c 2010-04-19
i think a lot but no result , anyone can help me? thank you
Thursday, April 15, 2010 10:01 AM
Answers
-
select * from ( select column1, column2, row_no = row_number() over (partition by column 1 order by abs(datediff(day, column2, getdate()))) from yourtable ) d where d.row_no = 1
KH TanThursday, April 15, 2010 10:08 AM
All replies
-
select * from ( select column1, column2, row_no = row_number() over (partition by column 1 order by abs(datediff(day, column2, getdate()))) from yourtable ) d where d.row_no = 1
KH TanThursday, April 15, 2010 10:08 AM -
You can use SELECT......, MAX(column2)...GROUP BY.....in your query so that the record with maximum Column2 value for each Column1 gets selected and hence is the one closest to current date.
Hope this helps.
Cheers!!
Muqadder.
Thursday, April 15, 2010 10:10 AM -
select column1, min(column2) from Table
group by column1
http://www.t-sql.ruThursday, April 15, 2010 10:10 AM -
Hi, this works for me:
Table prova(varchar(1), datetime) like this:
a 2010-04-15 00:00:00.000
a 2010-04-17 00:00:00.000
b 2010-04-17 00:00:00.000
b 2010-04-15 00:00:00.000
b 2010-04-20 00:00:00.000
c 2010-04-19 00:00:00.000
d 2010-04-12 00:00:00.000
d 2010-04-13 00:00:00.000
Query:
select column1, column2
from prova
where
( select count(*)
from prova as p
where p.column1 = prova.column1
and abs(datediff(day, p.column2, getdate())) < abs(datediff(day, prova.column2, getdate()))
) = 0
returns:
a 2010-04-15 00:00:00.000
b 2010-04-15 00:00:00.000
c 2010-04-19 00:00:00.000
d 2010-04-13 00:00:00.000
Hope this helps
Nicola
Thursday, April 15, 2010 12:16 PM -
thanks
if the table is like this:
column1 column2 column3
a 2010-04-15 bv
a 2010-04-17 vf
b 2010-04-17 gg
b 2010-04-16 re
b 2010-04-20 et
c 2010-04-19 io
how can i achieve a result with the value of colume 3
a 2010-04-15 bv
b 2010-04-16 re
c 2010-04-19 io
thanks
Thursday, April 15, 2010 2:55 PM -
thank youThursday, April 15, 2010 2:58 PM
-
thank youThursday, April 15, 2010 2:58 PM
-
select top 1 with ties *
from mytable order by row_number() over (partition by column1 order by column2)
http://www.t-sql.ruThursday, April 15, 2010 3:02 PM -
thanks
if the table is like this:
column1 column2 column3
a 2010-04-15 bv
a 2010-04-17 vf
b 2010-04-17 gg
b 2010-04-16 re
b 2010-04-20 et
c 2010-04-19 io
how can i achieve a result with the value of colume 3
a 2010-04-15 bv
b 2010-04-16 re
c 2010-04-19 io
thanks
you could if you try the query i posted
KH TanThursday, April 15, 2010 3:42 PM