First Instance of a Record - 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)

First Instance of a Record

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-11 : 09:52:11

I only want the first record of each of Column 2, but I still get all the records.

Here's the code:

DECLARE @Table1 table
(
Column1 int NOT NULL
, Column2 varchar(50) NOT NULL
, ColumnP varchar (3) NOT NULL
)

INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(1, 'Psi Serpentis ', 'ABC')
INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(2, 'Psi Serpentis ', 'PQR')
INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(3, 'Iota Horologii ', 'KLM')
INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(4, 'Epsilon Eridani', 'XYZ')
INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(5, 'Zeta 2 Reticuli', 'UVW')
INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(6, 'Zeta 2 Reticuli', 'JKL')
INSERT INTO @Table1 (Column1, Column2, columnP) VALUES(7, 'Sigma Draconis ', 'GHI')

DELETE FROM @Table1
WHERE Column1 = (SELECT MAX(Column1) FROM @Table1)

SELECT * FROM @Table1

(1 row(s) affected)
I'm getting this.....

Column1 Column2 ColumnP
----------- -------------------------------------------------- -------
1 Psi Serpentis ABC
2 Psi Serpentis PQR
3 Iota Horologii KLM
4 Epsilon Eridani XYZ
5 Zeta 2 Reticuli UVW
6 Zeta 2 Reticuli JKL

(6 row(s) affected)

But I'm trying to get this:

Column1 Column2 ColumnP
----------- -------------------------------------------------- -------
1 Psi Serpentis ABC
3 Iota Horologii KLM
4 Epsilon Eridani XYZ
5 Zeta 2 Reticuli UVW

Any help is greatly appreciated!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-11 : 10:03:29

where have you been Xerxes??? it's been a very long time

Will this do?


SELECT t1.*
FROM   @Table1 t1 
       join (SELECT min(Column1) as Column1, Column2 FROM @Table1 group by column2) t2 
         on t1.column1 = t2.column1 and t1.column2 = t2.column2

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-11 : 10:08:17

Thanks, Mladen

I was hoping, however, there was a simple MAX (or MIN) function I could issue without going through all this.

quote:
Originally posted by spirit1

where have you been Xerxes??? it's been a very long time
Will this do?

SELECT t1.*
FROM @Table1 t1
join (SELECT min(Column1) as Column1, Column2 FROM @Table1 group by column2) t2
on t1.column1 = t2.column1 and t1.column2 = t2.column2

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-11 : 10:16:40

Additional consideration.....I might not have an ID counter at the beginning (like Column1). What if all I had were duplicate names?

Also, Sigma Draconis fell off. Does this delete the last record, too?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-11 : 10:17:23

well if you don't care about the columnP value you could do

select min(column1), column2, max(columnsP)
from @table
group by column2

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-11 : 10:21:04

well if all you had was the names (only one column) then you could simply use distinct.

if not you have to group and aggregate records on something...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-11 : 10:28:14

quote:
Originally posted by spirit1

well if all you had was the names (only one column) then you could simply use distinct.

if not you have to group and aggregate records on something...

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Been awhile. Been real real real busy! And just reading what you gurus post!

THANKS AGAIN!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-11 : 12:56:32

quote:
Originally posted by spirit1

well if you don't care about the columnP value you could do

select min(column1), column2, max(columnsP)
from @table
group by column2

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

The problem with this solution is that it will not allow me to create a new table from this result!

select min(column1), column2, max(columnP)
INTO NEW_TAB <----- I need this!
from @table
group by column2

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-11 : 18:21:09

All columns in a table have to have a name.


select
	min(column1) as column1,
	column2,
	max(columnP) as columnP
INTO
	NEW_TAB
from
	@table
group by
	column2

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-08-16 : 12:37:05

quote:
Originally posted by Michael Valentine Jones

All columns in a table have to have a name.

select
min(column1) as column1,
column2,
max(columnP) as columnP
INTO
NEW_TAB
from
@table
group by
column2

CODO ERGO SUM

Thanks, Colonel....I had already figured it out. Sometimes I get in too great a hurry.

Thanks again!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources