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 - 31 through 45 (of 103 total)

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

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

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

You know what is really funny? It took me 15 minutes to put together that shot in the dark for you. It has been 4 hours and not a single response from you other than this sarcastic comment.

Obviously you really aren't interested in any kind of discussion regarding your question/problem, are you?

That's sad, really sad.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

It's probably slumber time.

Or he is waiting just to talk to his developers in India. Which developers will be asking the same questions, if they're worth their salt. Only difference is they will have access to the tables.

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

CirquedeSQLeil (1/10/2010)
It's probably slumber time.
Or he is waiting just to talk to his developers in India. Which developers will be asking the same questions, if they're worth their salt. Only difference is they will have access to the tables.

I foresee a similar question from India in the next 24 hours...:-P ... probably with sample data!!!:-D:-D:-D

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Gianluca Sartori (1/11/2010)
CirquedeSQLeil (1/10/2010)
It's probably slumber time.
Or he is waiting just to talk to his developers in India. Which developers will be asking the same questions, if they're worth their salt. Only difference is they will have access to the tables.
I foresee a similar question from India in the next 24 hours...:-P ... probably with sample data!!!:-D:-D:-D

As long as its marked "Urgent" and "I may lose my job" , then thats ok 🙂

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 5

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?

This will give up to 50 rows of distinct values:

SELECT DISTINCT TOP (50) variablename

You might like to go a step further to write:

SELECT COUNT(DISTINCT TOP (50) variablename)

...but that is not supported, sadly. You could write something like:

SELECT COUNT(*) FROM (SELECT DISTINCT TOP (50) variablename from table) T1;

as Gail suggested earlier.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 6

ifila (1/9/2010)
I think 'the drill' is BS! By the time i go thru all the things that email asks for, i can get the response elsewhere, for a LOT less $$$
If people dont want to help, then they can move along!
:rolleyes:

Are you paying for your answers???? I get mine for free.

- 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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Okay, twenty four hours and no response. Yep, definitely NOT a discussion on the OP's problem. Can't have a one way discussion, that is just a lecture or soliloquy.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Really unfortunate.

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

Paul White SSC Guru Points: 150467 More actions · Answer 9

Lynn Pettis (1/11/2010)
Can't have a one way discussion, that is just a lecture or soliloquy.

Steve, I think Lynn deserves some bonus points for correct use of the word 'soliloquy' :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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

Okay, now it has been over 2 days. Have you gotten an answer to your question? If you did, it would be nice if you let us in on the secret. You never know, someone else may have a similar problem and your solution may help them solve their problem. It is called being part of a community of professionals.

isuckatsql SSChampion Points: 10381 More actions · Answer 11

Hello guys, i have not forgotten you 😀

I guess the problem is a little more difficult than expected!

We do have big time zone differences to deal with.

isuckatsql SSChampion Points: 10381 More actions · Answer 12

I did get a couple of suggestions.

Use

CREATE PARTITION FUNCTION

or

CREATE PARTITION SCHEMA

.....but as you all know i suck at SQL, so i really dont know exactly how to use this to my benefit.

I hope to have a solution from my developers soon;-)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

ifila (1/12/2010)
I did get a couple of suggestions.
Use
CREATE PARTITION FUNCTION
or
CREATE PARTITION SCHEMA
.....but as you all know i suck at SQL, so i really dont know exactly how to use this to my benefit.
I hope to have a solution from my developers soon;-)

Now this makes no sense at all. Why don't you just give us the table definition(s) (CREATE TABLE statement(s)), sample data (INSERT INTO statements for the table(s)), and expected results based on the sample data? You may be surprised.

It only took me about fifteen minutes to put the code together I gave you two days ago.

isuckatsql SSChampion Points: 10381 More actions · Answer 14

USE [microsoft525]

GO

/****** Object: Table [dbo].[OutputResume3] Script Date: 01/12/2010 22:58:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[OutputResume3](

[ID3] [int] IDENTITY(1,1) NOT NULL,

[OriginalResume] [nvarchar](max) NULL,

[Email1] [nvarchar](1000) NULL,

[Email2] [nvarchar](1000) NULL,

[Email3] [nvarchar](1000) NULL,

[Email4] [nvarchar](1000) NULL,

[Email5] [nvarchar](1000) NULL,

[Email6] [nvarchar](1000) NULL,

[Email7] [nvarchar](1000) NULL,

[Email8] [nvarchar](1000) NULL,

[Email9] [nvarchar](1000) NULL,

[Email10] [nvarchar](1000) NULL,

[currentdateout] [nvarchar](50) NULL,

[resumeId] [int] NULL,

CONSTRAINT [PK_ID3] PRIMARY KEY CLUSTERED

(

[ID3] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

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

ifila (1/12/2010)
USE [microsoft525]
GO
/****** Object: Table [dbo].[OutputResume3] Script Date: 01/12/2010 22:58:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OutputResume3](
[ID3] [int] IDENTITY(1,1) NOT NULL,
[OriginalResume] [nvarchar](max) NULL,
[Email1] [nvarchar](1000) NULL,
[Email2] [nvarchar](1000) NULL,
[Email3] [nvarchar](1000) NULL,
[Email4] [nvarchar](1000) NULL,
[Email5] [nvarchar](1000) NULL,
[Email6] [nvarchar](1000) NULL,
[Email7] [nvarchar](1000) NULL,
[Email8] [nvarchar](1000) NULL,
[Email9] [nvarchar](1000) NULL,
[Email10] [nvarchar](1000) NULL,
[currentdateout] [nvarchar](50) NULL,
[resumeId] [int] NULL,
CONSTRAINT [PK_ID3] PRIMARY KEY CLUSTERED
(
[ID3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

Okay, that's 1/3 of what I asked for, how about the rest?

You don't need a lot of data, just enough to demonstrate the problem.