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

 

Answer 1

 

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 Tan

Answer 2

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.

Answer 3

select column1, min(column2) from Table

group by column1

---

http://www.t-sql.ru

Answer 4

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

Answer 5

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

Answer 6

thank you

Answer 7

thank you

Answer 8

select top 1 with ties *

from mytable order by row_number() over (partition by column1 order by column2)

---

http://www.t-sql.ru

Answer 9

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 Tan