Query to create higher or equal matched values

[jaryszek]

Hi Guys,

i have table like here:

So KeyName and DiskSize corresponding.

And what i want is to get this table:

So for keyName P6-64-Premium_SSD i have 3 keyNames which DiskSizes are bigger or equal: P6-64-Premium_SSD, P20-512-Premium_SSD, P30-1024-Premium_SSD.

For P20-512-Premium_SSD we have P20-512-Premium_SSD (equal) and higher value is P30-1024-Premium_SSD.

For P30-1024-Premium_SSD we have only equal value P30-1024-Premium_SSD.

Database sample in attachment,

How can i write query to achive this?
Please help,
Jacek

 

[theDBguy]

Hi. I’m already in bed but try looking into non-equi join queries. See you in the morning.

 

[jaryszek]

ok thank you,

i tried with:

SELECT TableSource.KeyName, TableSource_1.KeyName
FROM TableSource, TableSource AS TableSource_1
WHERE (([TableSource_1].[DiskSize]>[TableSource].[DiskSize]));

but it failed.

Jacek

 

[isladogs]

Try this

SELECT TableSource.KeyName, TableSource_1.KeyName AS HigherKeySizes
FROM TableSource, TableSource AS TableSource_1
WHERE (((TableSource.DiskSize)>=[TableSource_1].[disksize]))
ORDER BY TableSource.KeyName DESC;

 

[jaryszek]

o wow thank you very much!

Working like a charm!
Jacek

 

[isladogs]

You're welcome.
Here's another method but it can only be used in SQL view due to the non-equal join

SELECT TableSource.KeyName, TableSource_1.KeyName AS HigherKeySizes
FROM TableSource AS TableSource_1 INNER JOIN TableSource ON TableSource_1.DiskSize >= TableSource.DiskSize
ORDER BY TableSource.KeyName DESC;

BTW did you try out the approach I outlined recently about deleting specific duplicates?

 

[jaryszek]

hi Colin,

thanks !

Sorry my tasks changed and i didnt have time for it yet I will investigate.

Best,
Jacek