You must Sign In to post a response.
  • Category: SQL Server

    Distinct - with more then one column

    TABLE-VISITING

    Name Id Date
    USA 1 1.6.17
    UAE 2 2.6.17
    UK 3 3.6.17
    INDIA 4 4.6.17
    INDIA 5 5.6.17
    UK 6 6.6.17
    UAE 7 7.6.17
    USA 8 8.6.17
    UAE 9 9.6.17
    USA 10 10.6.17

    i need sql qury

    Date given -- > 7.6.17 to 10.6.17

    Condition --> Lessthen from date,Distinct Name,id max

    i need result
    india 5 5.6.17
    uk 6 6.6.17

    but my result came two time india and two time uk.
    distinct not work

    my sql qry
    select distinct name, id, date from visiting where date < 7.6.17 and name not in (select name from visiting where date between 7.6.17 and 10.6.17
  • #769302
    You can use RANK() function to get result in such scenarios. Here is the query:

    SELECT name,id,[date], RANK() OVER (PARTITION BY name ORDER BY id DESC) si
    INTO #temp_table
    FROM VISITING where [date] < '7.6.17';

    SELECT name,id,[date] FROM #temp_table where si = 1;

    Best Regards,
    Pritom Nandy
    http://programmerscampus.blogspot.com/


  • Sign In to post your comments