SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Grouping


Data Grouping

Author
Message
aanryn
aanryn
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 55
Hi everyone,

I am an novice in Sql. I have tried using group by, but I'm not able to get the desired output. my table is here say it as "Emp"
eno eid
1 1
2 2
3 3
4 3
5 3
6 4
7 4
8 4
9 4
10 5
11 5
12 5

I want the output as
eno eid
3 3
4 3
6 4
7 4
10 5
11 5
Can any one help me on this.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35892 Visits: 16699
No, not unless you give us some more information. Please post DLL in the form of CREATE TABLE scripts, sample data in the form of INSERT scripts, a brief summary of what you are trying to achieve, expected results, and what you have tried so far.

Thanks
John
aanryn
aanryn
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 55
Hi,
I have created a table
CREATE TABLE emp(eno int identity(1,1), eid int)

Inserted values

INSERT INTO emp VALUES (1)
INSERT INTO emp VALUES (2)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (5)
INSERT INTO emp VALUES (5)
INSERT INTO emp VALUES (5)

What i have tried
select max(eid) from emp group by eid having count(eid)>1
not getting desired output.

My result should be like this
eno eid
----------- -----------
3 3
4 3
6 4
7 4
10 5
11 5
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35892 Visits: 16699
I'm sorry, but I still can't work out what you're trying to do. Please will you describe in words what your query should achieve?

Thanks
John
Chandra Sekhara Vyas Dhara
Chandra Sekhara Vyas Dhara
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5143 Visits: 1149
Sorry there is no clarity in the post. What is the result you want to achieve? Please Post clearly.



aanryn
aanryn
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 55
I have two tables eno and eid in my emp table. I have inserted some values where eno is a primary key. So, the table looks like this

eno eid
---- ----
1 1
2 1
3 2
4 2
5 2
6 3
7 3
8 3
9 4
10 4

In eid column 1,2,3,4 is repeated several times.
I want from eid where data is 2 and 3 because it is repeated 3 times. I just want two rows from eno column either 3 or 4, 4 or 5, 3 or 5 and similarly for eid column with 3 i want 6 or 7, 7 or 8, 6 or 8. I just want only two times repetition of 2 and 3 in my result table

My result should look like

eno eid
---- ----
3 2
4 2
6 3
8 3

Could you please help me out.
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6911 Visits: 1865
Sorry still not clear...

"Keep Trying"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222624 Visits: 42003
I fully realize that this thread is 3-1/2 years old and the OP probably got a solution a very long time ago. I found this thread totally by accident while I was looking for something else. Since it was never answered, I though I'd take the time to provide a solution in case someone else might be looking for something similar.

This is simply a "Select top 2 from each group" problem with the added caveat that the groups we're selecting from must have at least 3 items. Here's the solution with some test data. As is normal, the details are in the comments in the code...

--=============================================================================
-- Create and populate a test table using the data from the original post.
-- Nothing in this section is a part of the solution to the problem.
-- We're just building test data here.
--=============================================================================
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
--===== Create the test table (no indexes included for this test)
CREATE TABLE #MyHead
(
eno INT,
eid INT
)
;
--===== Populate the test table with data from the original post
INSERT INTO #MyHead
(eno, eid)
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,4 UNION ALL
SELECT 7,4 UNION ALL
SELECT 8,4 UNION ALL
SELECT 9,4 UNION ALL
SELECT 10,5 UNION ALL
SELECT 11,5 UNION ALL
SELECT 12,5
;
--=============================================================================
-- One possible solution to the problem.
--=============================================================================
--===== Return the top 2 from each group having at least 3 rows in the group
SELECT mh1.*
FROM #MyHead mh1
WHERE eno IN ( --=== Find the eno's for only two rows for each eid
SELECT TOP 2
mh2.eno
FROM #MyHead mh2
WHERE mh2.eid = mh1.eid
)
AND mh1.eid IN ( --==== Find only those eid's that have at least 3 rows
SELECT eid
FROM #MyHead
GROUP BY eid
HAVING COUNT(*) >= 3
)
;



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9107 Visits: 5555
How about this Jeff ???

; WITH cte AS
(
SELECT *
,RowNum = ROW_NUMBER() OVER( PARTITION BY eid ORDER BY eno )
,GrpCnt = COUNT(*) OVER( PARTITION BY eid )
FROM emp
)
SELECT eno , eid
FROM cte
WHERE RowNum <= 2 AND GrpCnt > 2


SQL_By_Chance
SQL_By_Chance
SSC Eights!
SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)

Group: General Forum Members
Points: 995 Visits: 299
I think in the given scenario Jeff's solution with CTE is best possible.

But in case you want to dwell in subqueries. Can you please post the result that you expect.

Regards,
Ankit

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search