How to use LIMIT and IN together to have a default row in SQL?-CodePudding

I am exploring SQL with W3School

Here I want a default row where the customer name is Alfreds, then grab the remaining 29 rows to complete the query regardless of what their name is.

I tried to look on other SO question but they are too complicated to understand and using different syntax.

CodePudding user response：

What you are looking for is a specific order clause. Try this

SELECT * FROM Customers order by (case when CustomerName in ('Alfreds Futterkiste') then 0 else CustomerId end) limit 30 ;

CodePudding user response：

Use UNION to combine the two queries.

SELECT *
FROM Customers
WHERE CustomerName != 'Alfredo Futterkiste'
LIMIT 29
UNION 
SELECT *
FROM Customers
WHERE CustomerName = 'Alfreo Futterkiste'

CodePudding user response：

If you're going to have a default row in SQL you should really have that row in the table with a known primary key, and then UNION it onto your query:

--default row, that is always included as long as the table has a PK 1
SELECT *
FROM Customers
WHERE CustomerId = 1

UNION ALL
 
--other rows, a variable number of
SELECT *
FROM Customers
WHERE CustomerId <> 1 AND ...

LIMIT 30

The limit presented in this way applies to the result of the Union

If you ever want to do something where you're unioning together limited sets in other combinations you might want to look at eg a form like

(... LIMIT 2)
UNION ALL
(... LIMIT 28)