CODEWITHSUNDEEP
sqlsql-server-2005performancegroup-by
user278618
user278618

Reputation: 20242

Please for efficient query

I want to have efficient query to get some rows from my table.

Here is I think the best presentation of my table. 

-Somedate is not duplicated - it is date of modifiedon
-a,b,c are parent ids, let say countryCode
-1,2,3,4 are subparent, let say citycode
-guids are id of rows
-true, false are values of rows  - one can name this column - freshAir


a 1 GUID somedate true
a 1 GUID somedate true
a 2 GUID somedate false
a 2 GUID somedate false
b 3 GUID somedate false
b 3 GUID somedate false
b 3 GUID somedate false
b 4 GUID somedate false
c 5 GUID somedate true
c 6 GUID somedate true
c 6 GUID somedate false
c 6 GUID somedate false
c 7 GUID somedate false

I want the most recent rows MAX(modifiedon) grouped by countrycode and citycode and in this groups I need elements which have another values (true, false).

And in result I want:

a 1 GUID somedate true
a 2 GUID somedate false
c 5 GUID somedate true
c 6 GUID somedate false
c 7 GUID somedate false

Look that in result I don't want to have records with "b", because all rows have the same value (false).

EDITED:

IDResearch CountryCode  CityCode  ReligionCode Date
1          FR           PAR       CAT           11-11-2000
2          FR           PAR       CAT           11-11-2002
3          FR           STR       ISL           09-12-1975
4          FR           STR       ISL           09-12-1995
5          GR           ATE       PRA           09-12-1976
6          GR           ATE       PRA           09-12-1986
7          GR           SAL       PRA           09-12-1986
8          ES           BCN       ATH           01-07-2001
9          ES           BCN       ATH           01-08-2001
10         ES           MAD       CAT           01-07-2008
11         ES           VAL       CAT           01-07-2009

I want from tables Researches rows from countries where dominated religion is not the same. So In France in Paris dominated is Catholicism, but in Strasbourg is islam, so I want newest records from this country:

 2          FR           PAR       CAT           11-11-2002
 4          FR           STR       ISL           09-12-1995

Next, in Greece in all cities is the same relgion so I don't want records from this country.

In spanish cities dominated religion is not the same so I want records from Spain too

  9          ES           BCN       ATH           01-08-2001
  10         ES           MAD       CAT           01-07-2008
  11         ES           VAL       CAT           01-07-2009

I hope that will help and introduce more clarity.

Upvotes: 1

Views: 125

Answers (4)

user547541
user547541

Reputation:

The only problem Mark Byers' and Dog Ears' solution have is that they are canceling the countries only which have same religioncode in all rows. Now consider the scenario :

5          GR           ATE       PRA           09-12-1976
6          GR           ATE       PRA           09-12-1986
7          GR           SAL       PRA           09-12-1986
8          GR           SAL       ISL           11-01-1985

Logically, we should still omit GR as final research shows its all PRA! Those queries didn't cover that scenario I'm afraid. If in your case that's a problem, my solution is:

WITH LastResearch AS(
    SELECT R2.IDResearch,R2.countryCode,R2.CityCode,R2.ReligionCode,R2.Date FROM
    ((select countryCode,CityCode,max(Date) Date
        from researches R1
        Group by countryCode,CityCode)A
    INNER JOIN 
    researches R2
    ON (A.countryCode=R2.countryCode AND A.CityCode=R2.CityCode AND A.Date=R2.Date))),
FilteredCountry AS(
    SELECT countryCode FROM LastResearch
    GROUP BY countryCode
    HAVING COUNT(DISTINCT ReligionCode)>1
)
SELECT * FROM LastResearch
WHERE countryCode IN (SELECT countryCode FROM FilteredCountry)

Upvotes: 1

Dog Ears
Dog Ears

Reputation: 10005

Alternatively you can use Rank() function:

with countries as
(   select countryCode
    from researches r
    Group by CountryCode
    having COUNT(distinct REligionCode) > 1
),
partitioned as
(
    select   r1.*
            ,rank() over (partition by r1.countryCode, r1.cityCode order by r1.Date desc) as position
    from researches r1 
    join countries c on r1.CountryCode = c.CountryCode
)
select * from partitioned where position = 1

I'm not sure which option performs best.. maybe you could let us know?

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838116

Try this:

WITH countries AS
(
    SELECT countryCode
    FROM researches T1
    GROUP BY CountryCode
    HAVING COUNT(DISTINCT ReligionCode) > 1
),
max_rows AS
(
    SELECT researches.countryCode, cityCode, MAX(Date) AS Date
    FROM countries
    JOIN researches
    ON countries.countryCode = researches.countryCode
    GROUP BY researches.countryCode, cityCode
)
SELECT T2.*
FROM max_rows AS T1
JOIN researches AS T2
ON T1.cityCode = T2.cityCode
AND T1.countryCode = T2.countryCode
AND T1.Date = T2.Date

Upvotes: 0

Daniel
Daniel

Reputation: 855

I think you're looking for something like (postgreSQL):

SELECT *
FROM myTable
WHERE (countrycode, citycode, modifiedon) IN (SELECT countrycode, citycode, MAX(modifiedon) FROM myTable)

Upvotes: 0

Related Questions