ST Alpha - "doing that sort of meta-query in Postgres and MySQL is a bit painful ... but the first part can be easy" (Reading a Post)

SlickerTalk
Search Archives
The Leaderboard
The FAQ

Login
Create Account

Search Dr. S. Talk
TT/ST Wiki
How Well Do You Know ...

RSS Feed

Hosting by DigitalOcean

Support ST on Ko-Fi

Down to Post

Backboards:

Posts: 152

In response to "STIF: looking for some sql help" by zork

doing that sort of meta-query in Postgres and MySQL is a bit painful ... but the first part can be easy

Posted by Beryllium (aka grayman) Jun 11 '12, 14:02

SELECT dial_number, count(*) as attempts FROM call_table WHERE call_datetime = '2012-06-11' GROUP BY dial_number HAVING attempts < 3

Responses:

You will want to include the datatime variable in that subquery, then select the max(datetime) for each id from the subquery. - GregW Jun 11, 14:13 1
- thanks -- nm - zork Jun 11, 14:26
thanks. it's MS SQL btw. -- nm - zork Jun 11, 14:03
(might have to cast call-datetime to a real date for that to work) -- nm - Beryllium Jun 11, 14:03 8
- oh wait, I don't have number of daily attempts as a field. I need to figure that out as part of the query (i.e. count how many records have that date - zork Jun 11, 14:07 7
  - that's what count(*) as attempts does - it runs an aggregate calculation -- nm - Beryllium Jun 11, 14:19 2
    - yeah, I noticed that after but it's not working for me. Is that syntax mysql only? -- nm - zork Jun 11, 14:23 1
      - I don't know. I hate MSSQL, it's ass. - Beryllium Jun 11, 14:25
  - the dial number is from the start of the project -- nm - zork Jun 11, 14:08 3
    - I was assuming it was a phone number. You need some field that is consistent across the records in order to aggregate them. -- nm - Beryllium Jun 11, 14:20 2
      - so far I have this ... - zork Jun 11, 14:26 1
        
        I had meant to take out the part of the table name in the inner query to match the first mention. -- nm - zork Jun 11, 14:28

Post a message top

Replies are disabled on threads older than 7 days.