Get First row value

Question

Post reply

Get First row value

Viewing 15 posts - 16 through 30 (of 42 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

MID Status Code Number

1 A 1X 1

25 A 1X 2

37 T 1X 3

42 T 2X 1

58 A 2X 2

61 A 3X 3

If we did calculation as mentioned in the Number column, i think we can acheive our task.

I am not sure , just i am thinking.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 2

After that

select MID,Status,Code

from Emp

where Number = 1

will give the first row. Am i correct ?

karthik

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

Providing you set the number to 1 for the rows that you want returning. In the sample you gave, code 3x doesn't have a 1.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Oh...

MID Status Code Number

1 A 1X 1

25 A 1X 2

37 T 1X 3

42 T 2X 1

58 A 2X 2

61 A 3X 1

Sorry, i wrongly typed it.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

karthik M

One Orange Chip

Points: 29139

May 6, 2008 at 6:47 am

#811153

Any inputs ?

karthik

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

It should work.

What more do you want?

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

karthik M One Orange Chip Points: 29139 More actions · Answer 7

karthik M

One Orange Chip

Points: 29139

May 6, 2008 at 7:11 am

#811172

It should work.

Which one ?Kindly tell me.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 8

karthik M

One Orange Chip

Points: 29139

May 6, 2008 at 8:22 am

#811231

Any inputs ?

karthik

Luke L SSC-Dedicated Points: 35547 More actions · Answer 9

After that
select MID,Status,Code
from Emp
where Number = 1

It seems that you answered your own question in one of your previous posts.

You're creating a Temp table (or adding a column to your existing, I wasn't sure which) with a number column that you were going to populate and then doing the above select statement.

am I mistaken?

To help us help you read this[/url]For better help with performance problems please read this[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 10

No. I haven't given answer.

What i did was just suggested one way to accomplish this task.

MID Status Code Number
1 A 1X 1
25 A 1X 2
37 T 1X 3
42 T 2X 1
58 A 2X 2
61 A 3X 1
If we did calculation as mentioned in the Number column, i think we can acheive our task.
I am not sure , just i am thinking.

After that we can execute the below query

After that
select MID,Status,Code
from Emp
where Number = 1

Now i want to write a query to form Number column. Thats what i asked.

I hope i have explained clearly now.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 11

Luke,

You're creating a Temp table (or adding a column to your existing, I wasn't sure which) with a number column that you were going to populate and then doing the above select statement.

But i haven't done it. I want to add Number column in the mentioned format.

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Karthik - you're looking at implementing something like in Jeff's article on running aggregates.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Except that he's got nothing to do any ordering by, and nothing in the data that defines which the 'first' row is. Hence the necessity of the 'numbers' column in the first place

Karthik: your suggested query will work. How to get the numbers right is another matter. If there was any reasonable way to do it in SQL, we wouldn't be needing the numbers column in the first place, as there would already be a column that specified the priority order of statuses for each code

Hate to say it, but perhaps manually. How many different codes are there?

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 14

I vaguely remember some reference to 5 mil rows or something...

So Just outta curiosity, would the lowest MID value be the "first" record. I know this has already been asked and answered but, are you sure? Did it just happen that your example was written that way, or is that a realistic scenario in your data?

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 15

If you can make the assumption "Smallest MID is always the 'first' row for each code" then this should work:

-- Using MID (assumption: Smallest ID = "first")

SELECT PD.Code,

PD.Remarks,

E.Status

FROMPDetails PD

INNER JOINEmp E

ONE.MID = (SELECT MIN(MID) FROM Emp WHERE Code = PD.Code)

ORDER BY

PD.Code

.. Or, if you're going to use the aformentioned number column, this:

-- Using "number" instead

SELECT PD.Code,

PD.Remarks,

E.Status

FROMPDetails PD

INNER JOINEmp E

ONE.Code = PD.Code

AND E.Number = 1

ORDER BY

PD.Code

.. Hope this helps?