locked
Question about designign a SQL query RRS feed

  • 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 Tan
    • Edited by K H Tan Thursday, April 15, 2010 10:12 AM
    • Marked as answer by KJian_ Wednesday, April 21, 2010 6:10 AM
    Thursday, 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 Tan
    • Edited by K H Tan Thursday, April 15, 2010 10:12 AM
    • Marked as answer by KJian_ Wednesday, April 21, 2010 6:10 AM
    Thursday, 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.ru
    Thursday, 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 you
    Thursday, April 15, 2010 2:58 PM
  • thank you
    Thursday, 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.ru
    Thursday, 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 Tan
    Thursday, April 15, 2010 3:42 PM