selecting TOP row from a result set

Question

Post reply

selecting TOP row from a result set

Viewing 13 posts - 16 through 27 (of 27 total)

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

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 1

GilaMonster (1/13/2010)

shannonjk (1/13/2010)

If you only want to return one row of data per account number, then why return the address or phone number or any other data? It would be kind of strange that there is a business requirement to obtain random data per customer or account?

But the queries with Row number don't return random data. It's one account with that account's telephone number, address, etc in it.

Yes you are right in that statement but allow me to elaborate what I meant 😀

Before it was stated that there are single account numbers with multiple records. so account number 1 by that logic could have say 3 records. Also by that logic, if a DISTINCT statement did not return a singular value, that would mean one of the rows such as address, phone number, etc, would be different. In my mind I see results like that as 'random' from a business process standpoint not a coding standpoint. I thought your code was rather succinct and straight to the point 😉

Link to my blog http://notyelf.com/

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Oh, agreed. Which is why, when these questions come up (and they're quite frequent), I'm always very pointed in asking what the definition of 'first record' is.

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

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

Welsh Corgi (1/13/2010)

I believe all that I need is an outer and an inner sub query with a SELECT TOP and an Order by clause to get the first record.

Or you could look at the code I provided above and tell me where and why it doesn't do what 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

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 4

GilaMonster (1/13/2010)

Welsh Corgi (1/13/2010)

I believe all that I need is an outer and an inner sub query with a SELECT TOP and an Order by clause to get the first record.

Or you could look at the code I provided above and tell me where and why it doesn't do what you want.

Yes that code should be the solution. A subquery that includes SELECT TOP is not going to get you the results you are looking for.

Link to my blog http://notyelf.com/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I appreciate all the help and I regret for confusing anyone.

I'm sorry that I did not clarify that I could only use one table to remove the duplicates and I greatly appreciate the code.

I'm sorry if I offended anyone.

This is probably too much information.

I have a little bit of anxiety because about what I'm going to face.

I once inherited a Database that was initially created by a User. I was initially in Access.

Prior to my coming on site they hired a consultant to convert from Access to SQL Server.

He only moved the tables and left almost all of Local Access Queries, Select, Insert, Update and delete in Access.

This resulted in very bad performance as the business grew and more users started using the system.

I could not create FK Constraints on many duplicates and I was limited on the structural changes to the DB because it would require a significant amount of coding changes and processes which was not approved.

The system was eventually migrated to SAP with an Oracle Back-end.

This current situation is not ideal and if I was involved in the Analysis, Logical & Physical Design and implementation I would not be in this situation.

Thanks again for everyone input and effort.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

So does the code I gave you work or not? And if not, what's wrong with it?

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Your code is excellent and I appreciate your going out of your way.

I will archive it for future reference.

In this case I will only have one table to work with to remove the duplicates (Customers) in a poorly designed OLTP Database.

There may not be a child record.

Thank you so much and I regret that any grief that I caused you.

I have not had much sleep for 3 days and I missed some of your comments.

I did not intentional ignore.

Regrets and Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

No pun intended but I really got myself into the Dog House with this post:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

[font="Comic Sans MS"]Thank u All:-):-)

Thank u ....Thank u Gail Shaw 🙂

I used your Row_number code and It works like gem..........

I used account number in both places, instead of order by area

(PARTITION BY ACCT_NO ORDER BY ACCT_NO)

[/font];-)

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

SQL Learner-684602 (1/14/2010)

I used account number in both places, instead of order by area

(PARTITION BY ACCT_NO ORDER BY ACCT_NO)

In other words, you don't care which row you get back, you just want any one of them.

You sure there's no logic that says which row should be kept and which ones not?

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

The requirement requires that was provided is to return the first record created.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Welsh Corgi (1/14/2010)

The requirement requires that was provided is to return the first record created.

Then your order by needs to be on a column that stores the date created (I don't recall if you have one). What you've currently got allows SQL to order the 'duplicate' records in any way it likes. It may just happen that it's currently doing it in the order the rows were created, but you have no guarantee that it is or will continue to do so in the future

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

Paul White SSC Guru Points: 150457 More actions · Answer 13

Paul White

SSC Guru

Points: 150457

January 14, 2010 at 5:47 pm

#1104022

SQL Learner-684602 (1/14/2010)

(PARTITION BY ACCT_NO ORDER BY ACCT_NO)

That's broadly equivalent to:

(ORDER BY (SELECT NULL))

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi