how to fetch database table results using stored procedure and condition?

Question

I am new to Stored Procedure and I searched for the answer and could not find one working for me.

I have a table like below:

|---------------------|------------------|----------------------|
|     InterfaceID     |       Prefix     |      FinancialID     |
|---------------------|------------------|----------------------|
|        1501         |      ADM/B/11/   |          8           |
|---------------------|------------------|----------------------|
|        1501         |      ASD/K/10/   |          7           |
|---------------------|------------------|----------------------|
|        1502         |      BDM/H/22/   |          7           |
|---------------------|------------------|----------------------|
|        1503         |      CDM/Q/14/   |          6           |
|---------------------|------------------|----------------------|
|        1506         |      FDM/R/17/   |          8           |
|---------------------|------------------|----------------------|
|        1506         |      FDU/R/16/   |          7           |
|---------------------|------------------|----------------------|
|        1506         |      FDT/R/14/   |          6           |
|---------------------|------------------|----------------------|
|        1506         |      FDD/R/12/   |          5           |
|---------------------|------------------|----------------------|
|        1506         |      LMD/R/10/   |          4           |
|---------------------|------------------|----------------------|

I need an output like this

|---------------------|------------------|----------------------|
|     InterfaceID     |       Prefix     |      FinancialID     |
|---------------------|------------------|----------------------|
|        1501         |      ADM/B/11/   |          8           |
|---------------------|------------------|----------------------|
|        1502         |      BDM/H/22/   |          7           |
|---------------------|------------------|----------------------|
|        1503         |      CDM/Q/14/   |          6           |
|---------------------|------------------|----------------------|
|        1506         |      FDM/R/17/   |          8           |
|---------------------|------------------|----------------------|

That is, if want to select the one row for each InterfaceID with highest FinancialID.

I tried this select query:

select InterfaceID,Prefix,FinancialID 
from aInterfaceTable
WHERE Financialid=(select max(Financialid))

but it gives me an error

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

how to do this easily?

I have added the tag [tag:greatest-n-per-group]. This question has been answered many times on Stack Overflow, and most of these have been given this tag. — Bill Karwin, Apr 26 '18 at 05:17

score 0 · Answer 1 · 2018-04-26T05:49:47.873

0

SELECT interfaceid,Prefix, max(FinancialID) 
             FROM table 
             GROUP BY interfaceid

edited Apr 26 '18 at 05:49

answered Apr 26 '18 at 05:13

returns error `Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.` – Apr 26 '18 at 05:28
try the new query i edited. it will work – Apr 26 '18 at 05:50

score 0 · Accepted Answer · answered Apr 26 '18 at 05:25

0

I got it working

select i.InterfaceID,i.Prefix,i.FinancialID 
from aInterfaceTable i
WHERE Financialid=(select max(Financialid) from aInterfaceTable where InterfaceID = i.InterfaceID)

answered Apr 26 '18 at 05:25

so its done now? – Apr 26 '18 at 05:30

score 0 · Answer 3 · answered Apr 26 '18 at 05:37

Here is the query you need.

SELECT I.InterfaceID, I.Prefix, I.FinancialID
FROM INTERFACE I
INNER JOIN (
    SELECT InterfaceID, MAX(FinancialID) as HF
    FROM INTERFACE 
    GROUP BY InterfaceID
) IA ON I.InterfaceID = IA.InterfaceID AND I.FinancialID = IA.HF

Here is the working DB Fiddle that you can play with. https://www.db-fiddle.com/f/bcbheQNi2FfjjEKSxRw82q/0

score 0 · Answer 4 · answered Apr 26 '18 at 06:11

0

select a.*,Prefix from (select InterfaceID,max(FinancialID) FinancialID  from 
aInterfaceTable
group by InterfaceID) a
left join aInterfaceTable b on a.InterfaceID = b.InterfaceID and a.FinancialID 
= b.FinancialID

answered Apr 26 '18 at 06:11

Jyoti Vyas

1
1

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – rollstuhlfahrer Apr 26 '18 at 06:48
Thanks for advise – Jyoti Vyas Apr 28 '18 at 05:51

how to fetch database table results using stored procedure and condition?

4 Answers4