Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 top 10, distinct

Author  Topic 

aniljillella
Starting Member

4 Posts
Posted - 2008-07-14 : 04:48:50
TID EVENT CIC MSISDN
"11565063" "PLACECALL" 35 "9948763432"
"11466258" "PLACECALL" 12 "9948991671"
"11637678" "PLACECALL" 75 "9848111409"
"11637662" "PLACECALL" 69 "9951412831"
"11565125" "PLACECALL" 53 "9951044869"
"11796318" "PLACECALL" 182 "9705042816"
"12253533" "PLACECALL" 253 "9951218689"
"11565064" "PLACECALL" 35 "9951252131"
"11637679" "PLACECALL" 75 "9848360600"
"11565126" "PLACECALL" 53 "9948847941"
"11694177" "PLACECALL" 106 "9912924518"
"11565045" "PLACECALL" 60 "9912215090"

the above is the actual data i have. Now i require top 10 rows with distinct cic value. The result should be like this

TID EVENT CIC MSISDN
"11565063" "PLACECALL" 35 "9948763432"
"11466258" "PLACECALL" 12 "9948991671"
"11637678" "PLACECALL" 75 "9848111409"
"11637662" "PLACECALL" 69 "9951412831"
"11565125" "PLACECALL" 53 "9951044869"
"11796318" "PLACECALL" 182 "9705042816"
"12253533" "PLACECALL" 253 "9951218689"
"11694177" "PLACECALL" 106 "9912924518"
"11565045" "PLACECALL" 60 "9912215090"


cic values 35,53 and 75 were not repeated.

Please help me with this...its urgent

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 04:53:46
select tid, event, cic, msisdn
from (
select tid, event, cic, msisdn, row_number() over (partition by cic order by tid) as recid
from table1
) AS d
where recid = 1


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

aniljillella
Starting Member

4 Posts
Posted - 2008-07-14 : 05:05:16
I want top 10 records

some thing like this....

Selct top 10 tid,cic, msisdn from isupevents........ but the cic value should be distinct
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 05:11:16
It is not that hard to experiment...
select TOP 10 tid, event, cic, msisdn
from (
select tid, event, cic, msisdn, row_number() over (partition by cic order by tid) as recid
from isupevents
) AS d
where recid = 1


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts
Posted - 2008-07-14 : 05:17:07
quote:
Originally posted by aniljillella

TID EVENT CIC MSISDN
"11565063" "PLACECALL" 35 "9948763432"
"11466258" "PLACECALL" 12 "9948991671"
"11637678" "PLACECALL" 75 "9848111409"
"11637662" "PLACECALL" 69 "9951412831"
"11565125" "PLACECALL" 53 "9951044869"
"11796318" "PLACECALL" 182 "9705042816"
"12253533" "PLACECALL" 253 "9951218689"
"11565064" "PLACECALL" 35 "9951252131"
"11637679" "PLACECALL" 75 "9848360600"
"11565126" "PLACECALL" 53 "9948847941"
"11694177" "PLACECALL" 106 "9912924518"
"11565045" "PLACECALL" 60 "9912215090"

the above is the actual data i have. Now i require top 10 rows with distinct cic value. The result should be like this

TID EVENT CIC MSISDN
"11565063" "PLACECALL" 35 "9948763432"
"11466258" "PLACECALL" 12 "9948991671"
"11637678" "PLACECALL" 75 "9848111409"
"11637662" "PLACECALL" 69 "9951412831"
"11565125" "PLACECALL" 53 "9951044869"
"11796318" "PLACECALL" 182 "9705042816"
"12253533" "PLACECALL" 253 "9951218689"
"11637679" "PLACECALL" 75 "9848360600"
"11694177" "PLACECALL" 106 "9912924518"
"11565045" "PLACECALL" 60 "9912215090"


cic values 35 and 53 were not repeated.

Please help me with this...its urgent



note... in your test data '75' was repeated. any particular reason or just an error?

Em
Go to Top of Page

aniljillella
Starting Member

4 Posts
Posted - 2008-07-14 : 05:21:38
I am sorry thats an error
Go to Top of Page

aniljillella
Starting Member

4 Posts
Posted - 2008-07-14 : 05:23:25
Result of qry:
select TOP 10 tid, event, cic, msisdn
from (
select tid, event, cic, msisdn, row_number() over (partition by cic order by tid) as recid
from isupevents
) AS d
where recid = 1

"11466230" "PLACECALL" 4 "9848316055"
"11466371" "PLACECALL" 5 "9948734875"
"11466341" "PLACECALL" 6 "9912249147"
"11466423" "PLACECALL" 7 "9912779428"
"11466310" "PLACECALL" 10 "9948538435"
"11466258" "PLACECALL" 12 "9948991671"
"11466175" "PLACECALL" 18 "9951985263"
"11466284" "PLACECALL" 24 "9951220376"
"11466394" "PLACECALL" 30 "9848514552"
"11565063" "PLACECALL" 35 "9948763432"

This is the result that i got from the qry. But if we look at the actual table the top row has cic value 35, netx row 12,75......so on. the actual result should be like this

"11565063" "PLACECALL" 35 "9948763432"
"11466258" "PLACECALL" 12 "9948991671"
"11637678" "PLACECALL" 75 "9848111409"
"11637662" "PLACECALL" 69 "9951412831"
"11565125" "PLACECALL" 53 "9951044869"
"11796318" "PLACECALL" 182 "9705042816"
"12253533" "PLACECALL" 253 "9951218689"
"11694177" "PLACECALL" 106 "9912924518"
"11565045" "PLACECALL" 60 "9912215090"

these rows were the top 10 rows which has disctinct cic values.
The below 3 rows were skipped as thier cic values already exists

"11565064" "PLACECALL" 35 "9951252131"
"11637679" "PLACECALL" 75 "9848360600"
"11565126" "PLACECALL" 53 "9948847941"

note: cic values shouldnt be sorted,I completely require top 10 with distinct values as it is
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 05:28:46
Using only TOP 10 will get you any 10 random records.
In this case, the ROW_NUMBER() function will sort the records by tid column.

Add a final ORDER BY to get the records sorted the way you want.
SELECT TOP 10	Tid,
		Event,
		Cic,
		Msisdn
FROM		(
			SELECT	Tid,
				Event,
				Cic,
				Msisdn,
				ROW_NUMBER() OVER (PARTITION BY Cic ORDER BY Tid) AS RecID
			FROM	isupevents
		) AS d
WHERE		RecID = 1
ORDER BY	{Some mysterious undocumented column name here}


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-14 : 05:30:59
[code]SELECT IDENTITY(int,1,1)AS Seq,TID, EVENT, CIC, MSISDN
INTO #Temp
FROM YourTable

SELECT TOP 10 t.*
FROM #Temp t
INNER JOIN (SELECT MIN(Seq) AS MinRec,CIC
FROM #Temp
GROUP BY CIC) t1
ON t1.CIC=t.CIC
AND t1.MinRec=t.Seq
ORDER BY t.Seq

DROP TABLE #Temp[/code]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 05:33:24
Is this method guaranteed to insert the records into #Temp, the same order as they are stored and displayed originally?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-14 : 05:36:13
quote:
Originally posted by Peso

Is this method guaranteed to insert the records into #Temp, the same order as they are stored and displayed originally?


E 12°55'05.25"
N 56°04'39.16"

Nope, until you use an ORDER BY
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 05:49:20
So, in what way is the use of a temp table easier to use than a derived table with ROW_NUMBER() function?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-14 : 06:41:14
quote:
Originally posted by Peso

So, in what way is the use of a temp table easier to use than a derived table with ROW_NUMBER() function?


E 12°55'05.25"
N 56°04'39.16"

I just thought that would prevent the ordering of result based on CIC field as asked by OP.

DECLARE @test table
(
TID int,
EVENT varchar(100),
 CIC int,
 MSISDN bigint
)

INSERT INTO @test
SELECT  
11565063, 'PLACECALL', 35, 9948763432 UNION ALL
SELECT 11466258 ,'PLACECALL', 12, 9948991671 UNION ALL
SELECT 11637678, 'PLACECALL', 75 ,9848111409 UNION ALL
SELECT 11637662 ,'PLACECALL', 69, 9951412831 UNION ALL
SELECT 11565125, 'PLACECALL', 53, 9951044869 UNION ALL
SELECT 11796318 ,'PLACECALL', 182 ,9705042816 UNION ALL
SELECT 12253533 ,'PLACECALL', 253 ,9951218689 UNION ALL
SELECT 11565064 ,'PLACECALL', 35 ,9951252131 UNION ALL
SELECT 11637679 ,'PLACECALL', 75 ,9848360600 UNION ALL
SELECT 11565126 ,'PLACECALL', 53 ,9948847941 UNION ALL
SELECT 11694177 ,'PLACECALL', 106, 9912924518 UNION ALL
SELECT 11565045 ,'PLACECALL', 60, 9912215090


SELECT IDENTITY(int,1,1)AS Seq,TID, EVENT, CIC, MSISDN
INTO #Temp
FROM @test

SELECT TOP 10 t.*
FROM #Temp t
INNER JOIN (SELECT MIN(Seq) AS MinRec,CIC
            FROM #Temp
            GROUP BY CIC) t1
ON t1.CIC=t.CIC
AND t1.MinRec=t.Seq
ORDER BY t.Seq

DROP TABLE #Temp

output
-----------------------
Seq	TID	EVENT	CIC	MSISDN
1	11565063	PLACECALL	35	9948763432
2	11466258	PLACECALL	12	9948991671
3	11637678	PLACECALL	75	9848111409
4	11637662	PLACECALL	69	9951412831
5	11565125	PLACECALL	53	9951044869
6	11796318	PLACECALL	182	9705042816
7	12253533	PLACECALL	253	9951218689
11	11694177	PLACECALL	106	9912924518
12	11565045	PLACECALL	60	9912215090
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 06:45:48
Again, is this method guaranteed to insert the records into #Temp, the same order as they are stored in @temp?
SELECT IDENTITY(int,1,1)AS Seq,TID, EVENT, CIC, MSISDN
INTO #Temp
FROM @test


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-14 : 06:48:14
quote:
Originally posted by Peso

Again, is this method guaranteed to insert the records into #Temp, the same order as they are stored in @temp?
SELECT IDENTITY(int,1,1)AS Seq,TID, EVENT, CIC, MSISDN
INTO #Temp
FROM @test


E 12°55'05.25"
N 56°04'39.16"

Nope. not always.But how can we return the values in order of apperaence as asked by OP other than this?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-14 : 06:56:59
This is what I want OP to answer (because order of records has no meaning in a relational database)
by using "{Some mysterious undocumented column name here}" column name.

Either there is another column which to be sorted by, or the problem has no guaranteed solution.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site