Need help to implement the logic

Database
Enthusiast

Need help to implement the logic

Hi ,

I have a requirement to find the priorities from low to high 

I have a table :

TT_id   Priority_id

1201         1

1201         2

1202         1

1203         3

1203         1

1203         1

1203         4

1204         2

1204         3

I need to find the TT_id records which pariority_ids are raised from low to high .

Example:

1201 is raised from 1 to 2

1203 is rasied from 1 to 4

1204 is raised from 2 to 3

Please help me on these.

11 REPLIES
Ckp
Enthusiast

Re: Need help to implement the logic

SEL Id, MIN(priority), MAX(priority) FROM tmpTable group by 1

Senior Apprentice

Re: Need help to implement the logic

Chandra's query plus

HAVING MIN(priority) <> MAX(priority)
Ckp
Enthusiast

Re: Need help to implement the logic

yep, thanks dnoeth...

Enthusiast

Re: Need help to implement the logic

Thank you , so much for your replies.

Enthusiast

Re: Need help to implement the logic

incase if a new tt_id will enter into table as mentioned below

1207  3

1207  2

1207  1

should these record also considered , actually it shousld not be considered.

Please provide me a way where i can find tt's which are raised from low to high only..

Enthusiast

Re: Need help to implement the logic

Is there any other column to identify which record is loaded first?

Ckp
Enthusiast

Re: Need help to implement the logic

If you want to exclude such TT_IDs for which the priority got decreased later. Then you should add a Row_Load_Time column in your table which will capture the time of insertion of the record.

Then you can check that if the record with min(Row_Load_Time) has max(Priority) for a given TT_ID it should be excluded.

Hope this helps..

Enthusiast

Re: Need help to implement the logic

Hi CKP,

Thank you for answer, i want to implement a logic where i need to find a tt_id's which are increased in any manner (consider 1 to 6).

TT_id  Priority  Audit_timestamp

1201     1        datetime1

1201     4        datetime2

1205     4        datetime3

1205     2        datetime4

1205     1        datetime5

1205     3        datetime6

1202     2        datetime7

1202     1        datetime8

1204     1        datetime9

1205     6        datetime10

1203     4        datetime11

1203     3        datetime12

1203     2        datetime13

1203     1        datetime14

1207     1        datetime15

1207     2        datetime16

1208     4        datetime17

1208     5        datetime18

In this scenario i want to show the tickets which are increased from 1-6, i dont want to show records which are or not increased.

Please let me know how to implemet this logic.

Enthusiast

Re: Need help to implement the logic

SELECT oldVal.Id, OldVal.Priority AS OldValue, NewVal.Priority AS NewValue
FROM
(SELECT ID,Priority FROM
(SELECT Id,Priority, RANK () OVER (PARTITION BY ID ORDER BY Audit_TS) Rnk1
FROM <<TBL>>
)X WHERE Rnk1 = 1 GROUP BY 1,2
)OldVal
,
(SELECT X.ID,X.Priority, X1.Cnt FROM
(SELECT Id,Priority, RANK () OVER (PARTITION BY ID ORDER BY Audit_TS) Rnk1
FROM <<TBL>>)X
,(SELECT Id, COUNT(DISTINCT(Priority)) AS CNT FROM  <<TBL>>
GROUP BY 1) X1
WHERE X.Id =X1.Id
AND X.Rnk1 = X1.Cnt
)NewVal
WHERE Oldval.Id = NewVal.Id
AND OldVal.Priority < NewVal.Priority

I segreggated the min priorities for all TT_ID & max priorities for TT_Ids in 2 different parts. Then combined these 2 & checked if OldValue < NewValue.

There should be some other way too.