Select Records Issue

Question

Post reply

Select Records Issue

MuhammadShafiq

SSCrazy

Points: 2509
More actions
September 25, 2011 at 4:25 am

#390467

hi,
i have multiple records having FLAG field on the basis of which i will decide that this is paid or not....i want to show top 3 paid records only and then alll other records...how?

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

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Not enough detail here.

Please follow the link in my signature for details on how to post questions such as this.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 2

Actually i have search query like below

SELECT top 100 ID, COMPANY,

ADDRESS, Area, POSTALCODE, PROVINCE, PHONE1,

FAX, EMAIL, WEBSITE , city, lat, lon,FLAG, DBO.DISTANCE(LAT,LON,@LATITUDE,@LONGITUDE) AS DIST

FROM vwSearchResult

WHERE

comp_kws like '%' + @qry + '%' AND

locStr like '%' + @loc + '%'

ORDER BY DIST

This shows me alot of records....some have FLAG=0 and some have FLAG=1.

lets say there is 100 records where FLAG =1.

Now i want to show only 3 of them at the top and then show all other records having FLAG = 0...

hope this will clear to u!!

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

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

engrshafiq4 (9/25/2011)
hi,
i have multiple records having FLAG field on the basis of which i will decide that this is paid or not....i want to show top 3 paid records only and then alll other records...how?

Ugh! A FLAG "field" isn't very useful. It's virtually useless to index them because of the very low number of unique values they have. They're also virtually useless because they only tell you that, in this case, the item is "paid"... it doesn't tell you when it was paid and problems like this one become a real pain.

My suggestion would be to change your FLAG "field" to a "DatePaid" column and then this problem becomes almost child's play. If you can't change the column, then you really need to tell us what you think the "TOP 3" and "TOP 100" are according to the sort order of the columns you have. If the answer is "it doesn't matter", then I have to ask "then why bother" for this code? What is the business reason for this code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 5

After alot of googling i found solution like this

SELECT top 100 ID, COMPANY,

ADDRESS, Area, POSTALCODE, PROVINCE, PHONE1,

FAX, EMAIL, WEBSITE , city, lat, lon, DBO.DISTANCE(LAT,LON,@LATITUDE,@LONGITUDE) AS DIST

FROM vwSearchResult

WHERE

comp_kws like '%'+@qry+'%'

AND ID IN (

SELECT TOP 3 ID

FROM vwSearchResult

WHERE comp_kws like '%'+@qry+ '%' AND FLAG = 1 )

OR

ID IN (

SELECT TOP 100 ID

FROM vwSearchResult

WHERE comp_kws like '%'+@qry+ '%' AND FLAG = 0)

order by FLAG DESC

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

engrshafiq4 (9/25/2011)
After alot of googling i found solution like this
SELECT top 100 ID, COMPANY,
ADDRESS, Area, POSTALCODE, PROVINCE, PHONE1,
FAX, EMAIL, WEBSITE , city, lat, lon, DBO.DISTANCE(LAT,LON,@LATITUDE,@LONGITUDE) AS DIST
FROM vwSearchResult
WHERE
comp_kws like '%'+@qry+'%'
AND ID IN (
SELECT TOP 3 ID
FROM vwSearchResult
WHERE comp_kws like '%'+@qry+ '%' AND FLAG = 1 )
OR
ID IN (
SELECT TOP 100 ID
FROM vwSearchResult
WHERE comp_kws like '%'+@qry+ '%' AND FLAG = 0)
order by FLAG DESC

Heh... nicely done but it's just as I said... there's no significance to which rows have been returned for either set. So, now my curiosity is really up... If you don't mind me asking, what's the business reason for such a query?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 7

As i mentioned earlier,i want to show top 3 paid companies(paid companies are more than 3) on the top of my application display page and then show unpaid companies below

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Thanks... I get and got that. What I'm curious about is why you don't care with 3 companies paid and why you don't care which companies didn't. You'd think that there would be something like the top 3 companies that paid on time or list the top 100 companies by how much they owe. The totally random of the current selections make no sense to me.

Lemme guess... you're just following someone else's instructions and really don't know the business reason for this query... correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 9

Actually i am working under project manager...i do what he wants....

Leston...there is search on companies...so obviously we will show first those companies who pay us and will show later who do not pay us.....

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

engrshafiq4 (9/26/2011)
As i mentioned earlier,i want to show top 3 paid companies(paid companies are more than 3)

Top 3 by what criteria? Your query currently selects 3 companies with flag 1 (any three) with no other ordering. You could even get a different 3 every time you run the query.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

engrshafiq4 (9/26/2011)
Actually i am working under project manager...i do what he wants....

That's what I thought.

Just so you know, TOP in code has no sort-order implications without a "correct" ORDER BY and the ORDER BY on the FLAG column isn't what I'd consider a "correct" ORDER BY for the task at hand. You're not actually getting the "top" 3 companies that paid... you're getting "any" 3 companies that paid. You might want to ask your project manager if that's what they really intended. It's your neck if it's wrong and I'm trying to help you keep your neck out of the proverbial noose.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 12

Please help me in this...

i have more than 100000 records in company table...i want to export in excel and then do some correction and then import again.....i have a post for it but still no satisfactory answers.....

Phil Parkin SSC Guru Points: 245813 More actions · Answer 13

engrshafiq4 (9/26/2011)
Please help me in this...
i have more than 100000 records in company table...i want to export in excel and then do some correction and then import again.....i have a post for it but still no satisfactory answers.....

Based on the fact that you ignore multiple requests for information and do not provide anything like enough detail while asking your questions, I am not surprised.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 14

Actually most of my issues have been solved...now i do not know how to close those topics....

I think this should be clear to all of you....i have more than 100000 data in companies table in sqlserver2005 express...i want to export into excel...how i can do?