Page 1 of 1
Forum

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.

Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!




Share:
Notifications
Clear all

SQL Grouping for Paradox

6 Posts
2 Users
0 Likes
1,763 Views
(@jcbond)
Active Member
Joined: 3 years ago
Posts: 3
Topic starter  

Hi all
I'm trying to create a query that lists a group of records only when the last record of the group doesn't have a specific field value (trans<>'delete')
(I showed the list here grouped by access, ordered by access, date)

key access trans date (mm/dd/yyyy)
1 adt add 01/01/2020
2 adt change 01/02/2020
3 adt delete 01/03/2020
4 btt add 01/01/2020
5 btt change 01/02/2020
6 cdt add 01/01/2020
7 cdt change 01/02/2020
8 cdt delete 01/03/2020
9 ddt  add    01/01/2020
10 ddt  change 01/02/2020
11 ddt  change  01/03/2020
Select * from table1
exist where
(Select access, date
from table1
group by access, date
having trans <> 'delete')

I'm looking for this result: group without a last delete row

4 btt add 01/01/2020
5 btt change 01/02/2020
9 ddt  add    01/01/2020
10 ddt  change 01/02/2020
11 ddt  change  01/03/2020

 

In my other sql attempts, I'm only able to get the Delete row. SQL for Paradox tables seems to make this a difficult task. Any help is appreciated.
Thank you


   
ReplyQuote
Topic Tags
 Hans
(@hans)
Famed Member Admin
Joined: 10 years ago
Posts: 2556
 

Hello JCBond,

I haven't used Paradox in a very very long time, so I may not be able to help with a Paradox specific query.

However, in most SQL variants this should work, so I did a quick test in SQLite (database attached below).

SELECT * FROM table1 WHERE access NOT IN (SELECT access FROM table1 WHERE trans='delete');

 

Which gave me this:

 

This is the zipped test database:

 


   
ReplyQuote
(@jcbond)
Active Member
Joined: 3 years ago
Posts: 3
Topic starter  

Thanks for the quick response Hans.
Yes, Paradox is -old- and I have requested this database go to SQL Server long ago, but politics is slow...
That query in SQLite does work, but, unfortunately, Paradox sql can't do that


   
ReplyQuote
 Hans
(@hans)
Famed Member Admin
Joined: 10 years ago
Posts: 2556
 

You're welcome!

And here I thought Paradox supports sub-queries and "not in".
What part is not working? The "not in" or the sub-query?


   
ReplyQuote
(@jcbond)
Active Member
Joined: 3 years ago
Posts: 3
Topic starter  

The issue is the sub-query (at least).
Paradox sql does sub-queries but doesn't treat the sub-query as a group the way SQLite  does in that construction.
The sub-query results are just individual records, so removing a "Delete" transaction is just 1 record


   
ReplyQuote


 Hans
(@hans)
Famed Member Admin
Joined: 10 years ago
Posts: 2556
 

Interesting challenge for sure ... 🧐 

Unfortunately, testing Paradox would take quite a bit of work for me to even get started.
So I'm not sure how I can assist,... 😞 

 


   
ReplyQuote

Like what you see and you'd like to help out? 

The best way to help is of course by assisting others with their questions here in the forum, but you can also help us out in other ways:

- Do your shopping at Amazon, it will not cost you anything extra but may generate a small commission for us,
- send a cup of coffee through PayPal ($5, $10, $20, or custom amount),
- become a Patreon,
- donate BitCoin (BTC), or BitCoinCash (BCH).

Share: