SQL Comments and Questions

Over the years this site has been online (since 1998!) I've had many comments and questions emailed to me that don't quite fit into any other category on this site. I'll share a few here, with the senders' names and email addresses removed. Please feel free to leave new comments or questions here using the handy "Add A Comment" feature.

Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by TheMadProfessor on 2007-11-13 Rate this Comment

After ruminating over the 'display the unique date only once' question below, I finally came up with an answer that should work wholly within SQL. ('_' represents a literal of a single space.)

SELECT display_date, display_1, display_2, ...
(SELECT date_field, '1', date_field as display_date, '_' as display_1, '_' as display_2, ... (repeated however many times depending on how many fields of the records are wanted)
FROM (SELECT DISTINCT date_field FROM table_name)
SELECT date_field, '2', '_' as display_date, record_field_1 as display_1, record_field_2 as display_2, ...
FROM table_name)
ORDER BY 1, 2 (, ... if other order criteria wanted)

This should result such as follows (using the OP's example data and providing suitable ordering of records 1-n):
01/01/04 '_' '_' ...
'_' Record_1_field1 Record_1_field2 ...
'_' Record_2_field1 Record_2_field2 ...
'_' Record_3_field1 Record_3_field2 ...
01/02/04 '_' '_' ...
'_' Record_4_field1 Record_4_field2 ...
'_' Record_5_field1 Record_5_field2 ...
'_' Record_6_field1 Record_6_field2 ...

Display_date would also have to be defined such that the result value is a string, not a datetime (since the columns of the UNIONed table must have identical descriptions.) The easiest way would be something like the TO_CHAR function, assuming the particular DBMS had such a function defined. Of course, the whole problem becomes much simpler and less messy if you can use something to embed the SQL within such as VB, PL/SQL, etc. and process the table thru a cursor... Then you can just format your results appropriately as you run the cursor and check for date values to break.

Comment by TheMadProfessor on 2012-08-22 Rate this Comment

While rather ugly, this would probably work:

SELECT paired_field, COUNT(*) FROM
(SELECT field_1 + ' ' + field_2 AS paired_field FROM table_name)
GROUP BY paired_field
HAVING count(*) > 1;

Comment by Larry on 2007-10-09 Rate this Comment

Here is the SQL I came up with to get duplicate movie titles from a movie table:

Select movie_title, count(*)
from movie
HAVING count(*) > 1
group by movie_title

This might not work on all DBMSs, but should work high end ones like Oracle, SQL Server, DB2, etc.

Comment by archive on 2013-05-22 Rate this Comment

I am wondering if you can help me out.

I am trying to find the command to get all of the duplicates in a column in one of my tables. I know how to get distinct, but not how to get all of the duplicates.

Any idea?


Comment by archive on 2007-10-09 Rate this Comment

Dear Mr/Madam,

I am a DBA of our company, I meet a problem, I have a triger, It can update other table when this table being inserted. I successed it when I insert values only few colums, But When I use BCP as a tools insert data, I failed. Can you tell me the reason?


Comment by Larry on 2006-12-19 Rate this Comment

Sounds like you need a LIMIT and ORDER BY ASCENDING

FROM your_table_name
ORDER BY field_to_be_ordered ASCENDING

(Ordinarily you don't need to specify ascending as it is the default, and some DBMSs might not let you. As long as you can specify ascending it probably won't hurt.)

Comment by archive on 2006-12-19 Rate this Comment

I was wondering if you could help me ...

Here's what I am trying to do ...

How can I get the top 50 rows in Asending order if the Result set is a
1000 rows?

I don't know how much help you can provide, but any help is much

Comment by archive on 2006-12-19 Rate this Comment

That is a good idea, but it doesn't quite give me what I need. As a
result, I am looking for a count, and the pair. I don't think I was
clear in my description. And I only want the count and the pair if the
count would be greater than 1. (1 would be unique and distinct so it's

I will give an example.

table :
f_name l_name
john doe
john smith
jane smith
john doe
brad smith
jane smith
john doe

If I run the query on the preceeding table, it would return something
similart to this:
count f_name l_name
3 john doe
2 jane smith

All of the combinations that only appear once in the table, don't appear
in the results.

I realize that I forgot something in the select section of my example
query from the first e-mail.

SELECT count(*), field_1, field_2
FROM table_name
GROUP BY field_1, field_2
HAVING count(*) > 1;

Maybe I could use your query as a sub query and use a count(distinct) or something. I'm just not sure.

Comment by Larry on 2006-12-19 Rate this Comment

My first instinct was to use count with a correlated subquery, something like what you have - but with a subselect. However, you only want rows with duplicates of those two fields together, so I think we can get away with not using count. If you wanted more than two duplicates, then count would be necessary.

Now, I'm kind of assuming that you have a field that is a unique identifier for each row. If you don't you can try using rowid.

So, something like this might work:

SELECT a.field1, a.field2
from table_name as a, table_name as b (using the same table twice so we
can compare it to itself)
where a.field1 = b.field1
and a.field2 = b.field2
and a.rowid not = b.rowid; (use any unique row identifier you have here, so like custid or ordernum would work)

There are actually a bunch of ways you could do this.

Comment by archive on 2013-05-22 Rate this Comment

I have a rather complex sql question for you. I've been trying to figure it out for a while and google pointed me in your direction. If you don't want to have people harrassing you at all times, please disregard this e-mail and I appologize.

I have a table with many fields, two of which I'm concerned with at this time.

I would like to figure out many pairs of values from these two fields are duplicates.

My guess as to how this would work is:

SELECT count(*)
FROM table_name
GROUP BY field_1, field_2
HAVING count(*) > 1;

However, Oracle doesn't like this. I wish I had a copy of the error I was recieving, but I'm working over the phone with non-technical people. Is there a better way to do this? I'm considering a big nested complex ugly query right now (I haven't worked it through in my mind) but I would prefer to avoid such uglyness if I can.

Thank you for you time.

Comment by Larry on 2013-05-22 Rate this Comment

I have no idea. Certainly something that would be unique to Oracle.

Comment by archive on 2007-03-20 Rate this Comment

My son came home from Oracle Academy today and asked me this question:

How do I write a borrowed UID in sql and is there a specific sequence to write a borrowed UID?

I know a little bit about sql but not enough to answer that one. Have you ever heard of anything like that?

He is working on a sample Db for a class project and he may be referring to writing a sql query to cross transfer information from one table to another...maybe.

He said he asked his teacher and he did not know either. Go figure.

Any thoughts or links to info would be greatly appreciated.

Comment by Larry on 2013-05-22 Rate this Comment

Off the top of my head I'm not certian how I would handle that. Probably a subselect.

I guess the way you chose would partly depend on the size of your table and speed of your server. I've had queries that I thought were just lovely that ran very slowly, so I had to try a different approach.

Anyway, here's a pretty simple approach. I'm not setting up the tables and making certain that it is bug free, but you'll get the basic idea.

First, let's look at getting the last 20, which will be our subselect. I didn't see your table key listed, but I'll use it anyway.

FROM tbl_HC_Data
ORDER BY HC_date desc

(so, "limit 20" is the bit that only gets us only 20)

Then we tie that into a query for the best 10 scores

select *
FROM tbl_HC_Data
WHERE HC_key in (SELECT HC_key
FROM tbl_HC_Data
ORDER BY HC_date desc

Comment by archive on 2012-06-04 Rate this Comment

First let me thank you for your SQL examples on the Fluffy Cat website.

I was wondering if you could help me with a problem I am having and was not able to figure out by example of so far.

I am querying a database with ASP and need to get a subset of data from another subset

Basically I have a table of members who input scores by date.
I need to get the last 20 scores intered by date (Desc order) then I need the Top 10 lowest scores (Desc Order) from those 20 scores.

Table name tbl_HC_Data
Date = Date
Score = Round HC

Once I have the subset of Top 10 lowest scores from the last 20 entries I need to average that for the score average.

Any help you could give me or point me in the right direction would be greatly appreciated.

Comment by Larry on 2006-12-19 Rate this Comment

I believe triggers are only in the higher end DBMSs, such as Oracle. The basic idea of a trigger is to run every time a condition occurs. So, for example, you might have a trigger set to run every time a certain table is updated.

You would generally set up triggers when you designed and built your table. You should also be able to add, delete, or change a trigger later.

I think triggers are somewhat DBMS dependent, so you would need to look at
your platform for specific information.

Comment by archive on 2012-06-05 Rate this Comment

Dear sir,

i am a student from sri lanka and i want to find about some codes in sql.

1.0 how to create member,loan,book table in a library data base.

2.0 how to include sequence,views and indexes to be used in the data base.

3.0 how to populate the tables with the records.

4.0 how to create interactive scripts to prompt user for values to insert,update and delete records in the table

thank you.

looking forward to hear from you.


Comment by archive on 2006-12-12 Rate this Comment

I am trying to use SQl to show the results of a database showing the unique date once but continuing with the data found in that date. EX:
Record 1
Record 2
Record 3
Record 4
Record 5
Record 6

Where the database is showing (to make it simple)

DATE FIELD - Record Field
01/01/04 Record 1
01/01/04 Record 2
01/01/04 Record 3
01/02/04 Record 4
01/02/04 Record 5
01/02/04 Record 6

I don't want the date field showing 6 times. It has to be a DISTINCT or a double loop of some kind. Any clues?

Comment by archive on 2012-08-27 Rate this Comment

you have afine site here keep the good work going thanks

Comment by archive on 2006-12-12 Rate this Comment

Very clear and helpful, thanks for that website it really helped me alot for my study, you did a great job.

Comment by archive on 2007-03-20 Rate this Comment

Just a quick note to say thanks for putting together your SQL reference. I'll be bookmarking it.

Comment by Larry on 2013-04-03 Rate this Comment

I don't have my response to the question below, but I think I suggested that the query would have to be broken up. So, the program calling DB2 would set up an array to hold the responses, and would use a SQL call to get one row at a time. First you would call to get A1 B1 C1 D1, and put that result into your array. Then you call to get A2 B2 C2 D2, and put that into your array. etc. Sometimes breaking up a complex query is the easiest way to go.

Comment by archive on 2007-03-20 Rate this Comment

I have a SQL query that I am trying to write (in DB2 actually, though it may be a SQL-general question), but being such a novice can't seem to get it to work without causing a memory capacity error.

Say I have 6 tables that look like this:
A1 B1
A2 B2
A3 B3

B1 C1
B2 C2
B5 C5

C1 A1
C2 A2
C6 A6

A1 D1
A2 D2
A3 D3

D1 B1
D2 B2
D7 B7

C1 D1
C2 D2
C5 D5

And I want an answer set that looks like this:
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 -- D3
-- B5 C5 D5
A6 -- C6 --
-- B7 -- D7

what should my DB2 SQL query look like?

Comment by archive on 2008-01-04 Rate this Comment

The links provided on your site are useful... and i liked them.

I would like you guys to provide some links to sql performance tuning... and how to make your sql queries more efficient. It would be very useful if such links are provided


Sign in to comment on SQL Comments and Questions.