{Disarmed} [belajar-access] Re: MAX, BY GROUP dan memilih ID rekor

 

Misalnya, nama tabelnya @T

Silahkan coba query berikut ini.

SELECT A.NEAR_FID,B.* FROM @T A
INNER JOIN
(
SELECT IN_FID,MANGIN,MAX(NEAR_DIST) AS DATA FROM @T
GROUP BY IN_FID,MANGIN
) B
ON A.IN_FID=B.IN_FID AND A.MANGIN=B.MANGIN AND A.NEAR_DIST=B.DATA

salam hangat dan jabat erat,

Edy Wiyono

--- In belajar-access@yahoogroups.com, Zakirman Ibros <zibros1@...> wrote:
>
> Mohon bantuan:
>
> Bagaimana cara buat Query yang dapat menampilkan nila MAX, GROUP dan ID yang merupakan Primary Key.
>
> Saya mencoba untuk menggunakan FIRST atau LAST tetapi ternyata fungsi ini memilih yang akhir atau awal dalam urutan rekor, sementara rekor yang saya diinginkan lokasinya acak.
>
> Ini gambaran datanya:
>
> NEAR_FID    IN_FID    NEAR_DIST                   MAngin
> 1                  2       931.648207118691                        U2                  2       1116.54926872004                        T
> 3                  2       892.230579551041                        T
> 4                  2       925.854009319451                        S
> 5                  2       990.799258348377                        S
> 6                  2       958.285311671883                        B
> 7                  2       1184.95117820847                        B
> 8                  2       979.89939967956                         U
> 9                  2       931.648207118691                        U
> 10                3       637.214970151922                        B
> 11                3       453.77369093366                         B
> 12                3       924.457771711512                        B
> 13                3       812.451607032364                        U
> 14                3       826.222348998176                        U
> 15                3       885.546349977063                        T
> 16                3       997.579790073901                        T
> 17                3       812.773481150037                        S
> 18                3       637.70151922 348                        S
>
> saya membutuhkan data yang dihighligt dan bold, yang baru saya lakukan adalah 
>
> SELECT DataTbl.IN_FID, Max( DataTbl .NEAR_DIST) AS MaxOfNEAR_DIST, DataTbl .MAngin
> FROM DataTbl
> GROUP BY DataTbl .IN_FID, DataTbl .MAngin;
>
>
> tapi bagaimana cara memilih NEAR_FID
>
> Salam
> Zibros
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Members 11

Visit Your Group

SPAM IS PROHIBITED

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

if(window.yzq_d==null)window.yzq_d=new Object(); window.yzq_d['WhmQQkoGYrM-']='&U=13ck844le%2fN%3dWhmQQkoGYrM-%2fC%3d493064.14543979.14562481.13298430%2fD%3dMKP1%2fB%3d6060255%2fV%3d1';

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.