select - SQL Server Forums

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)

select

Author

Topic

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-06 : 14:19:51

HI

SEED NAME SEED TYPE COMPANY DELIVERY DATE
--------- ---------- -------- --------------
TOMATO 0 A 01.10.2005
CUCUMBER 1 A 01.12.2005
CUCUMBER 1 A 01.12.2005
TOMATO 1 B 01.1..2005
APPLE 0 B

HOW CAN I LIST THE LINES WHICH HAVE THE SAME SEED NAME, THE SEED TYPE AND THE SAME DELIVERY DATE?

AND ALL THE LINES WHOSE SEED NAMES,SEED TYPE AND DELIVERY DATES GIVE THE SAME WILL BE GIVEN THE SAME NUMBER

PLEASE HELP,,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-06 : 14:28:38

This should give you your list:


Select	[Seed Name]
	,[Seed Type]
	,[Delivery Date]
	--,count(*) as dupeCount
From	<yourTable>
Group by
	[Seed Name]
	,[Seed Type]
	,[Delivery Date]
Having count(*) > 1

Be One with the Optimizer
TG

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-06 : 14:38:11

Have a search on your keyboard for a caps lock key and try it out.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-06 : 16:39:03

ok but
I GIVE A NUMBER

Select [Seed Name]
,[Seed Type]
,[Delivery Date]
--,count(*) as dupeCount
From <yourTable>
Group by
[Seed Name]
,[Seed Type]
,[Delivery Date]

I wýll GIVE numbers from the begýnng to the end

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-06 : 17:45:07

You mean you want to create groups out of the entries and assign a value to each group.
easiest ay is to do it in two queries
if you want all entries not just the dups then get rid of the having clause in the first query.

select identity(int,1,1) as grp, *
into #a
from
(
Select [Seed Name]
,[Seed Type]
,[Delivery Date]
From <yourTable>
Group by
[Seed Name]
,[Seed Type]
,[Delivery Date]
having count(*) > 1
) a

select t1.*
from #a t2
join <yourTable> t
on t.[Seed Name] = t2.[Seed Name]
t.[Seed Type] = t2.[Seed Type]
t.[Delivery Date] = t2.[Delivery Date]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources