how to select top(8) distnict record

Question

5.00/5 (1 vote)

See more:

Dear sir,

i have these record
how to select
top 8 distnict record.
when i select then under line record repet (
same ZoneFrom and ZoneTo does not repeit.)

CSS

ZoneFrom    ZoneTo  ZoneFromName    ZoneToName  CreatedOn
19          18       MEG             MED         2011-12-30 03:59:13.070
41          38      FAR EAST          WCI       2011-12-30 50.700
38          31      WCI             SE ASIA     2011-12-30 03:58:30.327
19           31       MEG             SE ASIA   2011-12-30 03:58:11.327
19          38       MEG                WCI     2011-12-30 03:57:47.370
41          14      FAR EAST           ECI     2011-12-29 09:33:12.193
19           18     MEG               MED      2011-12-29 08:40:25.623
41           38      FAR EAST      WCI 2011-12-29 08:39:58.620
38          31        WCI          SE ASIA 2011-12-29 08:39:36.110
19          31       MEG          SE ASIA 2011-12-29 08:39:06.687
19           38  MEG                WCI      2011-12-29 08:38:36.690
41          14  FAR              EAST    ECI 2011-12-27 07:13:15.937
41          14  FAR EAST         ECI 2011-12-27 03:41:49.897
19           43  MEG            ARA 2011-12-22 08:21:43.150
41           38  FAR EAST            WCI 2011-12-22 08:21:20.870
38          31  WCI              SE ASIA 2011-12-22 08:20:54.040
19          31  MEG               SE ASIA 2011-12-22 08:20:28.893
19            38  MEG           WCI 2011-12-22 08:18:58.717
19            18  MEG           MED 2011-12-07 08:38:11.787
41        38  FAR EAST         WCI 2011-12-07 08:37:45.487
38         41  WCI            FAR EAST    2011-12-07 08:37:19.667
19         41  MEG         FAR EAST    2011-12-07 08:36:54.240
19         38  MEG            WCI 2011-12-07 08:36:27.380

Posted 29-Dec-11 22:53pm

DINESH K MAURYA

Add a Solution

Comments

AmitGajjar 30-Dec-11 5:07am

which query you are using ? can you post it here..

4 solutions

Solution 2

Hi,
Try This One..

SQL

SELECT a.* FROM tablename AS a
  LEFT JOIN tablename AS a2
    ON 
a.ZoneFrom = a2.ZoneFrom AND 
a.ZoneTo = a2.ZoneTo AND
a.ZoneFromName = a2.ZoneFromName AND
a.ZontToName = a2.ZoneToName AND
a.CreatedOn = a2.CreatedOn
HAVING COUNT(*) <= 8;

Posted 29-Dec-11 23:17pm

Prasad_Kulkarni

Updated 29-Dec-11 23:47pm

v2

Solution 1

when you select all columns for distinct then your Zonefromname column will contain repeated names cause your createdon contain differetn value
better to consider any one column for distinct, decide which column you wnat dictinct.

SQL

SELECT distinct top 8  Zonefromname FROM tablename

Posted 29-Dec-11 23:08pm

koolprasad2003

Comments

DINESH K MAURYA 30-Dec-11 5:12am

thanks for try to solve but
i want to select which record wher zone from and zone to does not match
zone to repet or zone from repet does not matter only zone to and zone from bothe not sam

Solution 3

If these are in a single table and you want top based on CreatedOn, perhaps something like

SQL

SELECT TOP 8 yt.ZoneFrom, yt.ZoneTo, yt.CreatedOn, ...
FROM YourTable yt
WHERE NOT EXISTS (SELECT 1
                  FROM YourTable yt2
                  WHERE yt2.ZoneTo = yt.ZoneTo
                  AND   yt2.ZoneFrom = yt.ZoneFrom
                  AND   yt2.CreatedOn < yt.CreatedOn)
ORDER BY yt.CreatedOn

Posted 29-Dec-11 23:24pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2011-12-30T00:16:00

SQL

DECLARE @ZoneInfo TABLE
(ZoneFrom   Bigint, ZoneTo  Bigint ,
ZoneFromName    NVARCHAR(50) , ZoneToName NVARCHAR(50) , CreatedOn DATETIME )


INSERT INTO @ZoneInfo
select 19,18,'MEG','MED','2011-12-30 03:59:13.070'
INSERT INTO @ZoneInfo
select 41,38,'FAR EAST','WCI','2011-12-30'
INSERT INTO @ZoneInfo
select 38,31,'WCI','SE ASIA','2011-12-30 03:58:30.327'
INSERT INTO @ZoneInfo
select 19,31,'MEG','SE ASIA','2011-12-30 03:58:11.327'
INSERT INTO @ZoneInfo
select 19,38,'MEG','WCI','2011-12-30 03:57:47.370'
INSERT INTO @ZoneInfo
select 41,14,'FAR EAST','ECI','2011-12-29 09:33:12.193'
INSERT INTO @ZoneInfo
select 19,18,'MEG','MED','2011-12-29 08:40:25.623'
INSERT INTO @ZoneInfo
select 41,38,'FAR EAST','WCI','2011-12-29 08:39:58.620'
INSERT INTO @ZoneInfo
select 38,31,'WCI','SE ASIA','2011-12-29 08:39:36.110'
INSERT INTO @ZoneInfo
select 19,31,'MEG','SE ASIA','2011-12-29 08:39:06.687'
INSERT INTO @ZoneInfo
select 19,38,'MEG','WCI','2011-12-29 08:38:36.690'
INSERT INTO @ZoneInfo
select 41,14,'FAR','EAST    ECI','2011-12-27 07:13:15.937'
INSERT INTO @ZoneInfo
select 41,14,'FAR EAST','ECI','2011-12-27 03:41:49.897'
INSERT INTO @ZoneInfo
select 19,43,'MEG','ARA','2011-12-22 08:21:43.150'
INSERT INTO @ZoneInfo
select 41,38,'FAR EAST','WCI','2011-12-22 08:21:20.870'
INSERT INTO @ZoneInfo
select 38,31,'WCI','SE ASIA','2011-12-22 08:20:54.040'
INSERT INTO @ZoneInfo
select 19,31,'MEG','SE ASIA','2011-12-22 08:20:28.893'
INSERT INTO @ZoneInfo
select 19,38,'MEG','WCI','2011-12-22 08:18:58.717'
INSERT INTO @ZoneInfo
select 19,18,'MEG','MED','2011-12-07 08:38:11.787'
INSERT INTO @ZoneInfo
select 41,38,'FAR EAST','WCI','2011-12-07 08:37:45.487'
INSERT INTO @ZoneInfo
select 38,41,'WCI','FAR EAST','2011-12-07 08:37:19.667'
INSERT INTO @ZoneInfo
select 19,41,'MEG','FAR EAST','2011-12-07 08:36:54.240'
INSERT INTO @ZoneInfo
select 19,38,'MEG','WCI','2011-12-07 08:36:27.380'


SELECT Top 8 * FROM (
select Distinct ZoneFrom,ZoneTo,ZoneFromName,ZoneToName  from
@ZoneInfo) ZITbl