Need help getting first value in a column

Question

Need help getting first value in a column

iluvmyelement

Mr or Mrs. 500

Points: 535
More actions
September 20, 2011 at 12:48 pm

#248310

For each ID/Sub ID combination where complete=0, I want the first value for location.
ID SubID Complete COL 3 Location
2000 0 0 1 GEORGIA
2000 0 0 2 TEXAS
2000 1 1 1 VERMONT
2000 1 0 2 ILLINOIS
2001 0 1 1 GEORGIA
2001 0 0 2 ARIZONA
2001 0 1 3 VERMONT
2001 0 0 4 ILLINOIS
2001 1 1 1 HAWAII
2001 1 1 2 FLORIDA
2001 1 1 3 MONTANA
2001 1 0 4 ILLINOIS
DESIRED RESULT SET SHOULD LOOK LIKE THIS:
2000 0 0 GEORGIA
2000 1 0 ILLINOIS
2001 0 0 ARIZONA
2001 1 0 ILLINOIS
For some reason I can't think of what I need to do (I'm having a bad day) - help!!!

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Define "first".

I don't see a column in there that indicates which one is first. Unless this is a text file or Excel spreadsheet or something like that. In a database table, there isn't a native first/last/3rd/whatever, the order has to be defined in the data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

iluvmyelement Mr or Mrs. 500 Points: 535 More actions · Answer 2

First would be based on the ID and SubID fields combined

gmrose SSC Eights! Points: 988 More actions · Answer 3

DECLARE @TABLE1 as TABLE (ROWSEQ INT IDENTITY, ID INT, SUBID INT, COMPLETE INT, LOCATION CHAR(20))

INSERT INTO @TABLE1

SELECT 2000, 0, 0, 'GEORGIA' UNION ALL

SELECT 2000, 0, 0, 'TEXAS' UNION ALL

SELECT 2000, 1, 1, 'VERMONT' UNION ALL

SELECT 2000, 1, 0, 'ILLINOIS' UNION ALL

SELECT 2001, 0, 1, 'GEORGIA' UNION ALL

SELECT 2001, 0, 0, 'ARIZONA' UNION ALL

SELECT 2001, 0, 1, 'VERMONT' UNION ALL

SELECT 2001, 0, 0, 'ILLINOIS' UNION ALL

SELECT 2001, 1, 1, 'HAWAII' UNION ALL

SELECT 2001, 1, 1, 'FLORIDA' UNION ALL

SELECT 2001, 1, 1, 'MONTANA' UNION ALL

SELECT 2001, 1, 0, 'ILLINOIS'

SELECT ID, SUBID, COMPLETE, LOCATION FROM @TABLE1

WHERE ROWSEQ IN

(SELECT MIN(ROWSEQ) AS ROWSEQ FROM @TABLE1

WHERE COMPLETE = 0

GROUP BY ID, SUBID)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

n_parker (9/20/2011)
First would be based on the ID and SubID fields combined

So what makes the first one Georgia and not Texas? Same ID, same SubID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

n_parker (9/20/2011)
First would be based on the ID and SubID fields combined

That does NOT define an order. There must be a column that will tell you what is "first". For example in the two rows:

2000 0 0 GEORGIA

2000 0 0 TEXAS

Why is Georgia first? What about Texas? If you order them by the first two columns, they can be in any order. You might get Texas one time and Georgia another time.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gmrose SSC Eights! Points: 988 More actions · Answer 6

Perhaps "first" means the order in which the data was inserted into the table. It's like "First come, first served". This may not be the perfect SQL definition for "first" but it does seem to be the common-place definition.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

gmrose (9/20/2011)
Perhaps "first" means the order in which the data was inserted into the table. It's like "First come, first served". This may not be the perfect SQL definition for "first" but it does seem to be the common-place definition.

Do you have an identity column on this table? If not, there is no way to guarantee that you get them in the order they were entered. In truth an identity will not either because it can be disabled but in practice it is 99.999% good enough. Do you have a DateEntered or anything that can help identify an order?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

gmrose (9/20/2011)
Perhaps "first" means the order in which the data was inserted into the table. It's like "First come, first served". This may not be the perfect SQL definition for "first" but it does seem to be the common-place definition.

The problem is that SQL doesn't necessarily honour that and is quite happy to return you second come, first served unless you explicitly tell it what you meant by 'first'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

iluvmyelement Mr or Mrs. 500 Points: 535 More actions · Answer 9

ok - just found a column with unique values that we could use. Let's call it COL3 (ADDED UP IN FIRST POST)

gmrose SSC Eights! Points: 988 More actions · Answer 10

gmrose

SSC Eights!

Points: 988

September 20, 2011 at 1:58 pm

#1384133

Disregard

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

n_parker (9/20/2011)
ok - just found a column with unique values that we could use. Let's call it COL3 (ADDED UP IN FIRST POST)

Something like this should work then. Notice I also put together some ddl and your sample data so it is easy to use. 😉

create table #temp

(

ID int,

SubID int,

Complete bit,

Location varchar(10)

)

insert #temp (ID, SubID, Complete, Location)

values

(2000, 0, 0, 'GEORGIA'),

(2000, 0, 0, 'TEXAS'),

(2000, 1, 1, 'VERMONT'),

(2000, 1, 0, 'ILLINOIS'),

(2001, 0, 1, 'GEORGIA'),

(2001, 0, 0, 'ARIZONA'),

(2001, 0, 1, 'VERMONT'),

(2001, 0, 0, 'ILLINOIS'),

(2001, 1, 1, 'HAWAII'),

(2001, 1, 1, 'FLORIDA'),

(2001, 1, 1, 'MONTANA'),

(2001, 1, 0, 'ILLINOIS')

select ID, SubID, Complete, Location from

(

select ID, SubID, Complete, Location, ROW_NUMBER() over (partition by ID, SubID order by ID, SubID, Location) as RowNum from #temp

where Complete = 0 --It looks like you only want those where complete is 0???

) result

where RowNum = 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

You edited your previous post while I was composing my response. Just change the order by in my above post to use Col3 and it should work for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

iluvmyelement Mr or Mrs. 500 Points: 535 More actions · Answer 13

For some reason that's just returning every row. Any other ideas? How about first location by alpha?

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

It returns EXACTLY what you asked for in your first post. What is the code you tried?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/