how to retrieve only one one......

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 2000 Forums

Transact-SQL (2000)

how to retrieve only one one......

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2008-03-05 : 03:26:37

Hi

1).

I have one table which columns are catid,prodid,prodct_name,date etc.
every catid has more than one records,

now i want to retrieve only one one records of every unique catid.

2).

and want to count records of every unique catid.

Regards,
ASIF

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-05 : 03:46:04

[code]1. Select catid, max(prodid) as prodid, max(prodct_name) as prodct_name, max(date) as date
from table
group by catid

2. select catid, count(*) as cnt
from table
group by catid[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 04:08:31

If combined

Select catid, max(prodid) as prodid, max(prodct_name) as prodct_name, max(date) as date,count(*) as counting
from table
group by catid

Madhivanan

Failing to plan is Planning to fail

asifbhura
Posting Yak Master

165 Posts

Posted - 2008-03-05 : 06:14:46

Thank You harsh_athalye & madhivanan both of you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 07:14:33

How can you be sure that

max(prodid) as prodid, max(prodct_name)

relates to same record?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 07:24:04

[code]-- Prepare sample data
DECLARE @Sample TABLE (CatID INT, ProdID INT, ProdName VARCHAR(200))

INSERT @Sample
SELECT 1, 1, 'Zweeck' UNION ALL
SELECT 1, 2, 'Aardvark' UNION ALL
SELECT 2, 3, 'SQLTeam' UNION ALL
SELECT 2, 4, 'Yak'

-- Harsh & Madhi
SELECT CatID,
MAX(ProdID) AS ProdID,
MAX(ProdName) AS ProdName
FROM @Sample
GROUP BY CatID

-- Peso
SELECT q.CatID,
q.ProdID,
s.ProdName
FROM @Sample AS s
INNER JOIN (
SELECT c.CatID,
(SELECT TOP 1 s.ProdID FROM @Sample AS s WHERE s.CatID = c.CatID ORDER BY NEWID()) AS ProdID
FROM (
SELECT DISTINCT CatID
FROM @Sample
) AS c
) AS q ON q.CatID = s.CatID
WHERE s.ProdID = q.ProdID[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources