Query problem - 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

SQL Server Administration (2000)

Query problem

Author

Topic

irehman
Starting Member

22 Posts

Posted - 2002-01-15 : 22:44:55

I have the following Data in one table.

LotNum Desc Tally_DT Tally_Site QtyRecv
101 Electronic 01/05/02 J 10
101 Electronic 01/06/02 J 20
*101 Electronic 01/07/02 J 30
102 Bottles 01/04/02 J 10
*102 Bottles 01/04/02 J 20
*103 Cups 01/02/02 J 50

I need to write a query that would give me records with an
asterick(*) sign. Because the records contains the most
recent date. Also, I need to group it by LotNum.
I can pull out Tally_DT and Lot_Num but as soon as I
Add more columns in select statement, it gives all the records for each lotNum.

Thanks in Advance

Edited by - irehman on 01/15/2002 22:45:24

Edited by - irehman on 01/15/2002 22:46:28

Edited by - irehman on 01/15/2002 22:46:53

Edited by - irehman on 01/15/2002 22:47:40

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-15 : 23:24:47

Hi

That is a really poor database design. You should have lotnum as an integer. Then have the flag in another column.

Your question is also a little vague. Yes, if you add more columns, and they are different, it will affect your grouping. What result are you trying to get ?

Damian

Nazim
A custom title

1408 Posts

Posted - 2002-01-15 : 23:33:46

select distinct t1.* from
tablename t1
inner join
(select lot_num,max(Tally_DT) MDT from tablename) t2
on t1.lot_num=t2.lot_num and t1.Tally_Dt=t2.MDT

HTH

----------------------------------
"True love stories don't have endings."

Edited by - Nazim on 01/15/2002 23:35:05

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources