How to select from records with bigger id

Question

I have a query like this,

     Select * from customers c join invoice I on
     c.id = i.customerid where c.date = '12-12-2019' and i.date = '12-12-2019'

The result is as follows

+------------+--------------+---------+-------+
| Customerid | customername | address | phone |
+------------+--------------+---------+-------+
| 1          | Peter        | Flat a  | 4567  |
+------------+--------------+---------+-------+
| 2          | Peter        | Flat a  | 4567  |
+------------+--------------+---------+-------+ 
| 5          | John         | Flat b  | 1234  |
+------------+--------------+---------+-------+
| 6          | John         | Flat b  | 1234  |
+------------+--------------+---------+-------+

The query will get the record with small customerid. How can I force the query to select the record with bigger customerid?

Answer 1

Try the following, here is the demo. you can write below query on the top of your main query.

select
    max(customerId) as customerId,
    customerName,
    address,
    phone
from customers
group by
    customerName,
    address,
    phone
order by 
    customerId

Output:

| customerId | customerName | address | phone |
| ---------- | ------------ | ------- | ----- |
| 2          | Peter        | Flat a  | 4567  |
| 6          | John         | Flat b  | 1234  |

Answer 2

As in the question I flagged as duplicate, you can use an exclusion join to filter your record set to retrieve the desired results.

Exclusion joins work by joining the same table and retrieving only the records with the greatest Customerid. Additionally it will not suffer from ONLY_FULL_GROUP_BY issues and does not require using GROUP BY with ORDER BY, which can produce undesirable results if used incorrectly.

Exclusion Join db-fiddle

SELECT c1.*
FROM customers AS c1
LEFT JOIN customers AS c2
ON c2.customername = c1.customername
AND c2.customerid > c1.customerid
WHERE c2.customerid IS NULL;

Result

| Customerid | customername | address | phone |
| ---------- | ------------ | ------- | ----- |
| 2          | Peter        | Flat a  | 4567  |
| 6          | John         | Flat b  | 1234  |

Ambiguous Exclusion Join

In the event that customername is not unique to a single entity (is ambiguous), you can include additional criteria to the exclusion join, to ensure the correct entity is retrieved.

LEFT JOIN customers AS c2
ON c2.customername = c1.customername
AND c2.address = c1.address
AND c2.phone = c1.phone
AND c2.customerid > c1.customerid
WHERE c2.customerid IS NULL;

---

Exclusion Join Expansion db-fiddle

You can then expand your statement with additional joins to retrieve records with the bigger customers.Customerid.

Given the following table data-sets and you want to display the customers.Customerid with 2, and 6

customers

| Customerid | customername | address | phone | date       |
| ---------- | ------------ | ------- | ----- | ---------- |
| 1          | Peter        | Flat a  | 4567  | 12-12-2019 |
| 2          | Peter        | Flat a  | 4567  | 12-12-2019 |
| 5          | John         | Flat b  | 1234  | 12-12-2019 |
| 6          | John         | Flat b  | 1234  | 12-12-2019 |

invoice

| Invoiceid | Customerid | date       |
| --------- | ---------- | ---------- |
| 1         | 1          | 12-12-2019 |
| 2         | 2          | 12-12-2019 |
| 3         | 5          | 12-12-2019 |
| 4         | 5          | 12-12-2019 |
| 5         | 6          | 12-12-2019 |

Query

SELECT c1.*, I.*
FROM customers AS c1
LEFT JOIN customers AS c2
ON c2.customername = c1.customername
AND c2.customerid > c1.customerid
INNER JOIN invoice I
ON c1.customerid = I.Customerid 
AND I.date = c1.date
WHERE c1.date = '12-12-2019'
AND c2.customerid IS NULL;

This will "force the query to select the record with bigger customerid?" of those also within invoice table.

Additionally I removed the redundant criteria c.date = '12-12-2019' and i.date = '12-12-2019' in favor of using I.date = c1.date in the INNER JOIN criteria.

I do not understand the purpose of customers.date, as usually invoices match to the customer that was invoiced, and the invoice dates entered are variable. So I believe that c.date = '12-12-2019' can be omitted in favor of using INNER JOIN invoice I ON c1.id = I.customerid AND I.date = '12-12-2019', in order to retrieve the invoices with the desired date and the associated customer that was invoiced.

Query Alternative

SELECT c1.*, I.*
FROM customers AS c1
LEFT JOIN customers AS c2
ON c2.customername = c1.customername
AND c2.customerid > c1.customerid
INNER JOIN invoice I
ON c1.customerid = I.Customerid 
AND I.date = '12-12-2019'
WHERE c2.customerid IS NULL;

Result

| Customerid | customername | address | phone | date       | Invoiceid | Customerid | date       |
| ---------- | ------------ | ------- | ----- | ---------- | --------- | ---------- | ---------- |
| 2          | Peter        | Flat a  | 4567  | 12-12-2019 | 2         | 2          | 12-12-2019 |
| 6          | John         | Flat b  | 1234  | 12-12-2019 | 5         | 6          | 12-12-2019 |

---

Considerations

It is important to note that your original statement join invoice I on c.id = i.customerid WHERE c.date = '12-12-2019' and i.date = '12-12-2019', only matches records from the invoice table with the same customerid and date in the customers table.

The query I provided forces retrieval of only customers with the bigger customers.Customerid. It does not match the invoice customer to the bigger Customerid, in other-words it will not retrieve the customers.Customerid = 2 when invoice.Customerid = 1.

If you are wanting to display the bigger customers.Customerid for associated invoices, please leave a comment and update your question to clarify along with providing a sample data-set with a desired end-result. However, I believe if that is the case, it is an indication of a relational design flaw in your database structure and may result in data-integrity issues in the long-term.