How do you limit records using Distinct and Count - TOP and Rowcount dont work ?

Question

How do you limit records using Distinct and Count - TOP and Rowcount dont work ?

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

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

ifila (1/10/2010)
SELECT COUNT(DISTINCT(variablename))
Let's assume i have 1000 variable names, with duplicates, which is why i need DISTINCT.
I only want to display the TOP 50.
SELECT TOP 50 COUNT(DISTINCT(variablename)) does not work.
SET ROWCOUNT 50 does not work.
What would be the correct syntax of the above statement?

Vague requirements. No sample data, no expected results. Again, where do we start?

isuckatsql SSChampion Points: 10381 More actions · Answer 2

Lynn,

You need to chill!

I can see that my lack of compliance is irritating you. I will just ask my Indian developers tomorrow.

Problem solved 🙂

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

ifila (1/10/2010)
Lynn,
You need to chill!
I can see that my lack of compliance is irritating you. I will just ask my Indian developers tomorrow.
Problem solved 🙂

I'm sorry, but why should I chill? You haven't explained your problem well enough and you expect an answer? You need to chill.

I'd be more than willing to help if I knew where to start.

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

Since you're not even willing (it appears) to answer my very simple questions, I'm quite happy to leave this to your highly qualified indian developers. I have better things to do than play 'Twenty Questions' on a Sunday evening.

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

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

Obscure help for an unclear question. Try this hints game:

from BOL on the TOP clause:

Specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE, and DELETE statements.

----------------------------------------------------------------------------------
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?

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

Troubled I am by your lack of discussion. Two-way street discussions are. Question you asked, but information unwilling you are to give.

Not knowing what it is you are trying to accomplish, here is a shot in the dark.

create table dbo.TestData (

ID int identity(1,1),

TestName varchar(32),

TestInfo uniqueidentifier

);

insert into dbo.TestData (

TestName,

TestInfo

)

select 'Able', NEWID() union all

select 'Baker', NEWID() union all

select 'Charlie', NEWID() union all

select 'Able', NEWID() union all

select 'Delta', NEWID() union all

select 'Echo', NEWID() union all

select 'Baker', NEWID() union all

select 'Able', NEWID() union all

select 'Foxtrot', NEWID() union all

select 'Charlie', NEWID() union all

select 'Hotel', NEWID() union all

select 'Foxtrot', NEWID() union all

select 'Tango', NEWID() union all

select 'Yankee', NEWID() union all

select 'Echo', NEWID() union all

select 'Hotel', NEWID();

select

ID,

TestName,

TestInfo

from

dbo.TestData

;

select COUNT(distinct TestName) from dbo.TestData;

-- select all records for the first 3 unique names in TestName

with UniqueNames (

TestName

) as (

select top (3)

TestName

from

dbo.TestData

group by

TestName

order by

TestName

)

select

td.ID,

td.TestName,

td.TestInfo

from

dbo.TestData td

inner join UniqueNames un

on (td.TestName = un.TestName);

drop table dbo.TestData;

So, how far off target am I? Are you willing to provide us with more information than the vague discriptions you have provided so far? If not, please take your complaints elsewhere. We are willing to help you if you are willing to help yourself.

EDIT: Changed it from 5 to 3.

isuckatsql SSChampion Points: 10381 More actions · Answer 7

......Help you i will, oh Jedi master.

The force is strong in this one!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

See also http://qa.sqlservercentral.com/Forums/FindPost844653.aspx

See also http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/50ebe46e-2ef5-41d9-b915-b4405ed4a15f

See also http://forums.asp.net/t/1447537.aspx :w00t:

See also http://sjc.ironspeed.com/post?id=3575239&goto=nextnewest

The earliest of these posts is six months ago.

Surely it's worth spending fifteen minutes setting up a little sample data and providing us with a sensible, logical and rational explanation of the problem?

Edited: per Lynn's request.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

Posted a response - but the tone of the thread has changed.

So how far off is Lynn's solution?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 10

ifila (1/10/2010)
......Help you i will, oh Jedi master.
The force is strong in this one!

Sacrasm, good. Now try answering the question instead.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 11

lobbymuncher (1/10/2010)
See also http://qa.sqlservercentral.com/Forums/FindPost844653.aspx
See also http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/50ebe46e-2ef5-41d9-b915-b4405ed4a15f
See also http://forums.asp.net/t/1447537.aspx :w00t:
See also http://sjc.ironspeed.com/post?id=3575239&goto=nextnewest
The earliest of these posts is six months ago.
Surely it's worth spending fifteen minutes setting up a little sample data and providing us with a sensible, logical and rational explanation of the problem?

Do you think you could edit your post and put the [ url ] [ /url ] (no spaces inside the brackets) around the url's? This would allow those of us who would like to see them to just click on them instead of having to cut and paste in our browsers.

Thanks.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 12

Lynn Pettis (1/10/2010)
ifila (1/10/2010)
......Help you i will, oh Jedi master.
The force is strong in this one!
...

I am waiting for the response on this one first. I saw his post in reply to your answer right after I posted my last post. I figured I would go back and withhold that post expecting that he would reply with some info.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

Lynn Pettis (1/10/2010)
Do you think you could edit your post and put the [ url ] [ /url ] (no spaces inside the brackets) around the url's? This would allow those of us who would like to see them to just click on them instead of having to cut and paste in our browsers.
Thanks.

Sure Lynn, original is edited.

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 14

lobbymuncher (1/10/2010)
Lynn Pettis (1/10/2010)
Do you think you could edit your post and put the [ url ] [ /url ] (no spaces inside the brackets) around the url's? This would allow those of us who would like to see them to just click on them instead of having to cut and paste in our browsers.
Thanks.
Sure Lynn, original is edited.
Cheers
ChrisM@home

Ahhh, better. Thanks Chris.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 15

lobbymuncher (1/10/2010)
Lynn Pettis (1/10/2010)
Do you think you could edit your post and put the [ url ] [ /url ] (no spaces inside the brackets) around the url's? This would allow those of us who would like to see them to just click on them instead of having to cut and paste in our browsers.
Thanks.
Sure Lynn, original is edited.
Cheers
ChrisM@home

Yes, thank you Chris.

Now, is it just me or do all of these posts seem to revolve around the same code or process as in this thread??