• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Problem with disticnt Rows

Hi Experts,

I have 2 tables, which are identical except for the concentrations in each table.

I tried query the date, conc1 and conc2, but I got 650000+ values, even though there are only 835 rows. So then I created a unique column with the row #, and was able to get 835 rows.

Now I only need value for distinct dates, each date has 2 value sets for the same date, but I only want to display one value per date. This is the SQL query I tried, and I am using VB6 with SQL thru ADODB connection to query these results to my flex grid.

I have also attached a sample excel file to of these 3 columns, and the result of my query

Thanks in advance
SELECT distinct  tbl_Operation.Date, tbl_Operation.Value, tbl_keelingPlot.Value2
FROM tbl_keelingPlot INNER JOIN tbl_Operation ON tbl_keelingPlot.Row = tbl_Operation.Row
ORDER BY tbl_Operation.Date;

Open in new window

0
Student_101
Asked:
Student_101
  • 7
  • 6
1 Solution
 
SharathData EngineerCommented:
You missed the attachment.
0
 
Student_101Author Commented:
0
 
SharathData EngineerCommented:

Is this the result of your query? So do you want only one record for a Date?
Which value you want to pick? Max or Min?
Can you post the sample set from your tables tbl_keelingPlot and tbl_Operation and the expected result?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Student_101Author Commented:
Hi Sharath,

I think picking an average would be a reasonable bet for the 2 values present per date.

If that's not possible, then either value can be queried, as the difference is minimal.

I have attached a sample from both tables here.

Thanks in advance.
sample-values-2.xls
0
 
SharathData EngineerCommented:
check this
SELECT t1.Date,t1.Value1,t2.Value2
  FROM (SELECT Date, MIN(Value) AS Value1 FROM tbl_Operation) t1
 INNER JOIN (SELECT Date, MIN(Value2) AS Value2 FROM tbl_keelingPlot) t2
    ON t1.Date = t2.Date
 ORDER BY t1.Date

Open in new window

0
 
Student_101Author Commented:
I get an error.

"Circular Reference caused by alias "value2" in query definition's select list"
0
 
SharathData EngineerCommented:
check this
SELECT t1.Date,t1.Value1,t2.Min_Value2
  FROM (SELECT Date, MIN(Value) AS Value1 FROM tbl_Operation) t1
 INNER JOIN (SELECT Date, MIN(Value2) AS Min_Value2 FROM tbl_keelingPlot) t2
    ON t1.Date = t2.Date
 ORDER BY t1.Date

Open in new window

0
 
Student_101Author Commented:
Hi sharath,

I will verify this tomorrow and confirm.

Appreciate your help.

Regards.
0
 
SharathData EngineerCommented:
no probs.
0
 
Student_101Author Commented:
Hi Sharath,

I tried the code, but I get an error again.

"You tried to execute a query that does not include the specified expression, 'Date' as part of an aggregate function"
0
 
Student_101Author Commented:
Sharath,

I was able to do it like this. This way I also found the average, and grouping by date solved the problem :)

Thanks for your help.

Regards
SELECT tbl_Operation.Date, Avg(tbl_Operation.Value) AS AvgOfValue, Avg(tbl_keelingPlot.Value2) AS AvgOfValue2
FROM tbl_keelingPlot INNER JOIN tbl_Operation ON tbl_keelingPlot.Row = tbl_Operation.Row
GROUP BY tbl_Operation.Date
ORDER BY tbl_Operation.Date;

Open in new window

0
 
SharathData EngineerCommented:
Student_101  -  Accept your post as solution and others as assisted. In that way you can close the question.
0
 
Student_101Author Commented:
For your kind help :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now