You must Sign In to post a response.

19 Jun 2017 18:23

Joined:	31/01/2010
Level:	Silver
Points:	166

Points:	5

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

02 Aug 2017 16:19

Joined:	06/04/2011
Level:	Gold
Points:	857

Points:	2

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;

Submit New Thread

Return to Return to Discussion Forum

Distinct - with more then one column

About dotnetspider.com

Quick Links

General

Services