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!


Forums
Software Developmen...
Delphi, Lazarus, Fr...
SQL Grouping for Pa...
 
Share:
Notifications
Clear all

[Solved] SQL Grouping for Paradox

 
Delphi, Lazarus, Free Pascal
Last Post by Hans 4 years ago
6 Posts
2 Users
0 Reactions
2,500 Views
RSS
 jcbond
(@jcbond)
Active Member
Joined: 4 years ago
Posts: 3
Topic starter February 12, 2021 11:39 AM  

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
sql paradox
 Hans
(@hans)
Famed Member Admin
Joined: 12 years ago
Posts: 2880
February 12, 2021 12:07 PM  

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:

Attachment : sql-test.png

 

This is the zipped test database:

 

Attachment : test-sql.db.zip

   
ReplyQuote
 jcbond
(@jcbond)
Active Member
Joined: 4 years ago
Posts: 3
Topic starter February 12, 2021 5:16 PM  

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: 12 years ago
Posts: 2880
February 13, 2021 8:41 AM  

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
(@jcbond)
Active Member
Joined: 4 years ago
Posts: 3
Topic starter February 19, 2021 5:46 PM  

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: 12 years ago
Posts: 2880
February 20, 2021 6:28 AM  

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
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  sql (4) , paradox (1) ,
Share: