top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Finding TOP X records from each group.

+2 votes
261 views
Finding TOP X records from each group.
posted Jun 16, 2015 by Manikandan J

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Create a table named photo_test and insert some test data as :-

create table photo_test
(
pgm_main_Category_id int,
pgm_sub_category_id int,
file_path varchar(MAX)
)

insert into photo_test values
(17,15,'photo/bb1.jpg');     

insert into photo_test values(17,16,'photo/cricket1.jpg');                                                    
insert into photo_test values(17,17,'photo/base1.jpg');                                                       
insert into photo_test values(18,18,'photo/forest1.jpg');                                                       
insert into photo_test values(18,19,'photo/tree1.jpg');                                                           
insert into photo_test values(18,20,'photo/flower1.jpg');                                                     
insert into photo_test values(19,21,'photo/laptop1.jpg');                                                       
insert into photo_test values(19,22,'photo/camer1.jpg');                                                 

insert into photo_test values(19,23,'photo/cybermbl1.jpg');                                                    
insert into photo_test values
(17,24,'photo/F1.jpg');

There are three groups of pgm_main_category_id each with a value of 17 (group 17 has four records),18 (group 18 has three records) and 19 (group 19 has three records). 
Now, if you want to select top 2 records from each group, the query is as follows:-

select pgm_main_category_id,pgm_sub_category_id,file_path from
(
select pgm_main_category_id,pgm_sub_category_id,file_path,
rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid
from photo_test
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by pgm_main_category_id,pgm_sub_category_id

The result is as:-

pgm_main_category_id    pgm_sub_category_id  file_path
17                           15                           photo/bb1.jpg
17                      16                      photo/cricket1.jpg
18                    18                    photo/forest1.jpg
18                           19                           photo/tree1.jpg
19                    21                    photo/laptop1.jpg
19                           22                           photocamer1.jpg
answer Jun 17, 2015 by Shivaranjini
Similar Questions
+1 vote
SQL: Get the highest number of each group and combine the result per parent column? 
id  |  group  |  parent  |  value
1       123        1         10
2       123        1         25
3       224        1         12
4       224        1         22
5       225        2         18
6       225        2         10
7       326        2         18
8       326        2         35

Now I need to grab the highest number of each group and combine the result per parent column so the wanted result would be like the following:

group  |  parent  |   value
123         1          47
225         2          53
0 votes
Populating fact table with key's from other tables

I hope you guys could help me with solving the following problem.
I am new to SQL (using SQL Server Management Studio 2017).

My goals is to combine the keys of different tables into one table.
Which is shown below. For this example i have 4 source tables of which i only display the keys;

ContractTable:

+------------+---------+
| ContractId | PartyId |
+------------+---------+
| C01        | 1       |
+------------+---------+
| C02        | 2       |
+------------+---------+
| C03        | 3       |
+------------+---------+
| C04        | 4       |
+------------+---------+
| C05        | NULL    |
+------------+---------+
<!-- end snippet -->

PartyTable:

+---------+
| PartyId |
+---------+
| P1      |
+---------+
| P2      |
+---------+
| P3      |
+---------+
| P4      |
+---------+
| P5      |
+---------+
| P6      |
+---------+
| P7      |
+---------+
| P8      |
+---------+

RentalObjectTable:

+----------------+------------+----------+
| RentalObjectId | ContractId | ObjectId |
+----------------+------------+----------+
| R1             | C5         | O1       |
+----------------+------------+----------+
| R2             | NULL       | O2       |
+----------------+------------+----------+
| R3             | C4         | O3       |
+----------------+------------+----------+
| R4             | C1         | O4       |
+----------------+------------+----------+

ObjectTable:

+----------+
| ObjectId |
+----------+
| O1       |
+----------+
| O2       |
+----------+
| O3       |
+----------+
| O4       |
+----------+
| O5       |
+----------+
| O6       |
+----------+

Together they should form the Key/FactTable

+---------+------------+----------------+----------+
| PartyId | ContractId | RentalObjectId | ObjectId |
+---------+------------+----------------+----------+
| P1      | C01        | R4             | O4       |
+---------+------------+----------------+----------+
| P2      | C02        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P3      | C03        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P4      | C04        | R3             | O3       |
+---------+------------+----------------+----------+
| P5      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P6      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P7      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P8      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| NULL    | C5         | R1             | O1       |
+---------+------------+----------------+----------+
| NULL    | NULL       | R2             | O2       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O5       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O6       |
+---------+------------+----------------+----------+

Uptill now i tried the following:

CREATE TABLE FACTS(
ObjectId NVARCHAR(255),
RentalObjectId NVARCHAR(255),
ContractId NVARCHAR(255),
PartyId NVARCHAR(255)
);

INSERT INTO FACTS(ContractId)
SELECT ContractId
FROM ContractTable

UDATE FACTS
SET FACTS.Party_id = PartyTable.PartyId
FROM FACTS FULL OUTER JOIN
PartyTable on PartyTable.PartyId = FACTS.PartyId;

Using that same update statement for the other field.
However this does not work.
I do not get the values on which the query does not find a match.

...