Re: SQL question, TOP 5 and all OTHERS - Mailing list pgsql-novice

From Skylar Thompson
Subject Re: SQL question, TOP 5 and all OTHERS
Date
Msg-id 20220606211213.dntvfnkwauk52fzc@thargelion
Whole thread Raw
In response to Re: SQL question, TOP 5 and all OTHERS  (Jean MAURICE <mauricejea@numericable.fr>)
List pgsql-novice
On Mon, Jun 06, 2022 at 09:46:12PM +0200, Jean MAURICE wrote:
> Hi Scott,
> what about using a Common Table Expression and the clause WITH ?
> I am not at home now but you can write something like
> 
> WITH top5 AS (SELECT vendor_name AS vendor_name,
> 
> ?????? count(DISTINCT inv_id) AS "# of Invoices"
> 
> FROM SpendTable
> 
> GROUP BY vendor_name
> 
> ORDER BY "# of Invoices" DESC
> 
> LIMIT 5)
> SELECT * FROM top5
> UNION
> 
> SELECT 'all other' AS vendor_name,
> 
> ?????? count(DISTINCT st.inv_id) AS "# of Invoices"
> 
> FROM SpendTable AS st
> 
> WHERE st.vendor_name NOT IN (SELECT vendor_name FROM top5)
> 
> ORDER BY "# of Invoices" DESC

There might be a challenge with ties, especially if you don't order by the
vendor name since you could get different results even on the same data
set, depending on how the query plan goes. It depends on what the OP is
looking for, I guess.

-- 
-- Skylar Thompson (skylar2@u.washington.edu)
-- Genome Sciences Department (UW Medicine), System Administrator
-- Foege Building S046, (206)-685-7354
-- Pronouns: He/Him/His



pgsql-novice by date:

Previous
From: Jean MAURICE
Date:
Subject: Re: SQL question, TOP 5 and all OTHERS
Next
From: mahendrakar s
Date:
Subject: Need help with moving test cases in extension folder